Why Does MySQL Throw 'Invalid use of group function' and How to Fix It Using HAVING?

MySQL error: "Invalid use of group function" causes and solutions

In the given MySQL query, the "Invalid use of group function" error is due to the use of the COUNT function in the WHERE clause. WHERE clause filters rows before grouping, causing error.

Use the HAVING clause to solve the problem

To resolve this issue, replace the WHERE clause with a HAVING clause. The HAVING clause filters groups after grouping. In this example, the HAVING clause should check the count of supplier ID (sid) to make sure it is greater than or equal to 2.

The following is the corrected subquery:

<code class="language-sql">(
    SELECT c2.pid
    FROM Catalog AS c2
    WHERE c2.pid = c1.pid
    HAVING COUNT(c2.sid) >= 2
)</code>

Complete query with HAVING clause

Using the updated subquery, the complete query becomes:

<code class="language-sql">SELECT c1.pid                      -- 选择 pid
FROM Catalog AS c1                 -- 来自 Catalog 表
WHERE c1.pid IN (SELECT c2.pid   -- 其中 pid 在以下集合中：
    FROM Catalog AS c2             -- pids
    WHERE c2.pid = c1.pid
    HAVING COUNT(c2.sid) >= 2     -- 至少有两个对应的 sids
);</code>

This query will return the pids of parts supplied by at least two different suppliers.

The above is the detailed content of Why Does MySQL Throw 'Invalid use of group function' and How to Fix It Using HAVING?. For more information, please follow other related articles on the PHP Chinese website!