mysql group function_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?

回答1:

You need to use HAVING, not WHERE.

The difference is: the WHERE clause filters which rows MySQL selects. Then MySQL groups the rows together and aggregates the numbers for your COUNT function.

HAVING is like WHERE, only it happens after the COUNT value has been computed, so it'll work as you expect. Rewrite 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)

回答2:

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.

来源：https://stackoverflow.com/questions/2330840/mysql-invalid-use-of-group-function