Tag Archives: Invalid use of group function

MYSQL Error: Invalid use of group function [How to Solve]

select
	orderNumber,
	sum(priceEach * quantityOrdered) as totalvalues
from
	orderdetails
where 
	sum(priceEach * quantityOrdered) > 60000;

Error reporting reason:

This is because the aggregate function is used in the where statement.

Functions and differences between where and having:
Where is a constraint declaration that constrains the query conditions in the database before the result of the query is returned, that is, it works before the result is returned, and “aggregate function” cannot be used after where, because the execution order of where is before the aggregate function.

Having is a filtering declaration. Filtering is performed after the result of querying the database is returned, that is, it works after the result is returned, and “aggregation function” can be used after having. Note: having is used to filter the found results. You cannot use having for values that are not found. I always thought that having must be used together with group by. I tried it today. It’s just a screening condition. There’s no stress on binding.

Correct writing:

select
	orderNumber,
	sum(priceEach * quantityOrdered) as totalvalues
from
	orderdetails
group by
	orderNumber
having sum(priceEach * quantityOrdered) > 60000;

Another thing to note is that SQL syntax:

SELECT [DISTINCT|DISINCTROW|ALL] select_ expression,… — Query results
[from table_references — specify the table to query
[where where_definition] – where clause, filter criteria for query data
[group by col_name,…]– Group the query results of [matching where clause]
[having where_definition] – conditionally restrict the grouped results
[order by {unsigned_integer | col_name | formula} [ASC | desc],…]– Sort query results
[limit [offset,] rows] – limit the number of displayed results
[procedure procedure_name] – query the result set data returned by the stored procedure
]

The writing order of query statements must follow the above rules.