[mysql] MySQL: Invalid use of group function

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?

First, the error you're getting is due to where you're using the COUNT function -- you can't use an aggregate (or group) function in the WHERE clause.

Second, instead of using a subquery, simply join the table to itself:

SELECT a.pid 
FROM Catalog as a LEFT JOIN Catalog as b USING( pid )
WHERE a.sid != b.sid
GROUP BY a.pid

Which I believe should return only rows where at least two rows exist with the same pid but there is are at least 2 sids. To make sure you get back only one row per pid I've applied a grouping clause.