MySQL Error: 'Invalid use of group function'—WHERE vs. HAVING: When Should I Use Which?

MySQL Error: Invalid use of grouping function

When you encounter the "Invalid use of grouping function" error when using MySQL, be sure to understand the difference between the WHERE and HAVING clauses.

In the provided query, the goal is to find parts supplied by at least two suppliers. Initially, you try to use a WHERE clause to compare each pid to the output of a subquery that computes sids. However, MySQL performs the WHERE before aggregation, making this approach ineffective.

The correct solution is to use the HAVING clause. HAVING filters the results of aggregate functions such as COUNT(), allowing you to perform post-aggregation calculations. By rewriting the subquery using HAVING, as shown in the answer:

(
  SELECT c2.pid
  FROM Catalog AS c2
  WHERE c2.pid = c1.pid
  HAVING COUNT(c2.sid) >= 2
)

You can get the results you want. The HAVING clause will ensure that only parts with at least two suppliers are included in the final output.

The above is the detailed content of MySQL Error: 'Invalid use of group function'—WHERE vs. HAVING: When Should I Use Which?. For more information, please follow other related articles on the PHP Chinese website!