MySQL: Invalid use of group function

Question

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?

Answer 1

You have to use HAVING, not WHERE like this:

Try rewritting your subquery as:

(                  -- 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.