CODEWITHSUNDEEP
mysql
elMeroMero
elMeroMero

Reputation: 952

mysql 1111 invalid use of group function

This query works perfectly fine:

SELECT leads.contact_id, count(leads.contact_id) as orders 
FROM leads
WHERE leads.status="sold" 
GROUP BY leads.contact_id

But this one doesn't. Why?

SELECT leads.contact_id, count(leads.contact_id) as orders
FROM leads
WHERE orders > 1 and leads.status="sold" 
GROUP BY leads.contact_id

When executing this query I get the error

1054 - Unknown column 'orders' in 'where clause'

Even when replacing orders by count(leads.contact_id) it doesn't work:

SELECT leads.contact_id, count(leads.contact_id) as orders
FROM leads
WHERE count(leads.contact_id) > 1 and leads.status="sold" 
GROUP BY leads.contact_id

Returning following error message:

1111 - Invalid use of group function

Upvotes: 0

Views: 62

Answers (2)

Eric
Eric

Reputation: 3257

If you understand SQL order of operation, you will know why your query won't work.

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

WHERE clause is evaluated before SELECT and GROUP BY. So it won't know what count(leads.contact_id) or orders is. HAVING on the other than is evaluated after GROUP BY. It is used to filter aggregates.

SELECT leads.contact_id, count(leads.contact_id) as orders 
FROM leads
WHERE leads.status="sold" 
GROUP BY leads.contact_id  
HAVING orders > 1

Upvotes: 1

elMeroMero
elMeroMero

Reputation: 952

I should have used HAVING instead of WHERE because WHERE can only be used for filtering the original data table not the computed one. Yet the column count(leads.contact_id) or orders is a computed column not an original column of the table therefore I had to use HAVING like this to make it work:

SELECT leads.contact_id, count(leads.contact_id) as orders 
FROM leads
WHERE leads.status="sold" 
GROUP BY leads.contact_id  
HAVING orders>1

Also see this detailed explanation

Upvotes: 0

Related Questions