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?

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 sids. 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:

  1. Save the inner aggregation into a variable
select @avg:=avg(close) from prices;
  1. 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

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x