MySQL. Queries. Unknown column

Question

I have this following query and I want to display the results where masini > 2 but when I run the query it says that 'masini' is not an existing column but it's the name of a custom column I defined on the first row. I am new to MySQL.. can anyone point me in the right direction? This is my query:

SELECT pers.serie_buletin AS persoana, COUNT(prop.serie_buletin) AS masini 
FROM persoana pers 
JOIN proprietate prop
ON pers.id_persoana = prop.serie_buletin
WHERE masini > 2
GROUP BY persoana ;

I defined the column on this line, in this part "COUNT(prop.serie_buletin) AS masini" but it says "Error Code: 1054. Unknown column 'masini' in 'where clause'". What am I missing?

score 2 · Accepted Answer · answered May 23 '14 at 19:08

Change WHERE to HAVING.

GROUP BY persoana 
HAVING masini > 2;

The MySQL HAVING clause is used in the SELECT statement to specify filter conditions for group of rows or aggregates.

The MySQL HAVING clause is often used with the GROUP BY clause. When using with the GROUP BY clause, you can apply a filter condition to the columns that appear in the GROUP BY clause. If the GROUP BY clause is omitted, the MySQL HAVING clause behaves like the WHERE clause. Notice that the MySQL HAVING clause applies the condition to each group of rows, while the WHERE clause applies the condition to each individual row.

source

Three great answers but I'll mark yours as being the best. Fully explained and everything. Thank you. — MrSilent, May 23 '14 at 19:10

score 2 · Answer 2 · edited May 23 '17 at 12:06

The where clause is evaluated first, so MySQL don't know what is masini there. Here are some similar questions.

Getting unknown column error when using 'as' in mysql statement

Unknown Column In Where Clause

As explained in the questions above and another answers here, you can only use alias from sub-queries, or in clauses that are evaluated after the alias is assigned as ORDER BY, GROUP BY or HAVING, in your case you can use the having clause.

score 1 · Answer 3 · answered May 23 '14 at 19:08

1

SELECT pers.serie_buletin AS persoana,
       COUNT(prop.serie_buletin) AS masini 
FROM persoana pers 
JOIN proprietate prop
ON pers.id_persoana = prop.serie_buletin
GROUP BY pers.serie_buletin
HAVING COUNT(prop.serie_buletin) > 2;

answered May 23 '14 at 19:08

Bob

1,045
8
10

1

In the `HAVING` clause, which occurs after `GROUP BY`, you can refer to masini by name. – Marcus Adams May 23 '14 at 19:14
Ahh - I live in an Oracle world - Oracle doesn't seem to support that. Thanks for the information. Now I know mysql does! – Bob May 23 '14 at 19:24

score 1 · Answer 4 · answered May 23 '14 at 19:08

You can't put column aliases in the where clause. Ever.

In this case, though, you actually need a having clause:

SELECT pers.serie_buletin AS persoana, COUNT(prop.serie_buletin) AS masini 
FROM persoana pers 
JOIN proprietate prop
ON pers.id_persoana = prop.serie_buletin
GROUP BY persoana 
HAVING masini > 2;

MySQL. Queries. Unknown column

4 Answers4