MySQL: Invalid use of group function

699 Asked by GrahamCook in SQL Server , Asked on Jun 15, 2021

I am using MySQL. Here is my schema:

Suppliers(sid: integer, sname: string, address string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

(primary keys are bolded)

I am trying to write a query to select all parts that are made by at least two suppliers:

-- Find the pids of parts supplied by at least two different suppliers.
SELECT c1.pid                      -- select the pid
FROM Catalog AS c1                 -- from the Catalog table
WHERE c1.pid IN (                  -- where that pid is in the set:
    SELECT c2.pid                  -- of pids
    FROM Catalog AS c2             -- from catalog
    WHERE c2.pid = c1.pid AND COUNT(c2.sid) >= 2 -- where there are at least two corresponding sids
);

First off, am I even going about this the right way?

Secondly, I get this error:

1111 - Invalid use of group function

What am I doing wrong? Why getting error mysql invalid use of group function?

Answered by Jordan Duncan

You have to use HAVING, not WHERE like this:

Try rewritting your subquery as shown below:

(                  -- where that pid is in the set:

SELECT c2.pid                  -- of pids

FROM Catalog AS c2             -- from catalog

WHERE c2.pid = c1.pid

HAVING COUNT(c2.sid) >= 2)

Note: The WHERE clause is used to filter the rows selected in MySQL. Then MySQL groups the rows together and aggregates the numbers for the COUNT function. HAVING clause is similar to the WHERE clause, but it only happens after the COUNT value has been computed. This will help you preventing mysql invalid use of group function error.

MySQL: Invalid use of group function

Your Answer