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   gt   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

User · Answer

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

User · Answer

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   gt   2

[mysql] MySQL: Invalid use of group function

Examples related to mysql

Examples related to sql

Examples related to mysql-error-1111