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?

## Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

### Method 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)

### Method 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 `sid`

s. To make sure you get back only one row per `pid`

I’ve applied a grouping clause.

### Method 3

If you don’t have an aggregate function in your where clause, another possible source of the `1111 - Invalid use of group function`

error is if you have nested aggregate functions:

select sum(avg(close)) from prices; (1111, 'Invalid use of group function')

You can get around this by breaking up the problem into two steps:

- Save the inner aggregation into a variable

select @avg:=avg(close) from prices;

- Run the outer aggregation against the variable

select sum(@avg) from prices;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0