MySQL - Using COUNT in the WHERE clause

Question

I am trying to accomplish the following in MySQL  see pseudo code   SELECT DISTINCT gid FROM  gd  WHERE COUNT     gt  10 ORDER BY lastupdated DESC   Is there a way to do this without using a  SELECT     in the WHERE clause because that would seem like a waste of resources

User · Answer

COUNT    can only be used with HAVING and must be used after GROUP BY   statement Please find the following example    SELECT COUNT     M Director PID FROM Movie INNER JOIN M Director ON Movie MID   M Director MID  GROUP BY M Director PID HAVING COUNT     gt  10 ORDER BY COUNT    ASC

User · Answer

try  SELECT DISTINCT gid FROM  gd  group by gid having count     gt  10 ORDER BY max lastupdated  DESC

User · Answer

I m not sure about what you re trying to do    maybe something like  SELECT gid  COUNT    AS num FROM gd GROUP BY gid HAVING num  gt  10 ORDER BY lastupdated DESC

User · Answer

COUNT    can only be used with HAVING and must be used after GROUP BY   statement Please find the following example    SELECT COUNT     M Director PID FROM Movie INNER JOIN M Director ON Movie MID   M Director MID  GROUP BY M Director PID HAVING COUNT     gt  10 ORDER BY COUNT    ASC

User · Answer

i think you can not add count   with where  now see why       where is not same as having   having means you are working or dealing with group and same work of count   it is also dealing with the whole group     now how count it is working as whole group  create a table and enter some id s and then use    select count    from table name   you will find the total values means it is indicating some group   so where does added with count

User · Answer

try  SELECT DISTINCT gid FROM  gd  group by gid having count     gt  10 ORDER BY max lastupdated  DESC

User · Answer

I m not sure about what you re trying to do    maybe something like  SELECT gid  COUNT    AS num FROM gd GROUP BY gid HAVING num  gt  10 ORDER BY lastupdated DESC

User · Answer

Just academic version without having clause   select   from      select gid  count    as tmpcount from gd group by gid   as tmp where tmpcount  gt  10

User · Answer

SELECT COUNT    FROM  gd  GROUP BY gid HAVING COUNT gid   gt  10 ORDER BY lastupdated DESC    EDIT  if you just want the gids    SELECT MIN gid  FROM  gd  GROUP BY gid HAVING COUNT gid   gt  10 ORDER BY lastupdated DESC

User · Answer

try  SELECT DISTINCT gid FROM  gd  group by gid having count     gt  10 ORDER BY max lastupdated  DESC

User · Answer

try this   select gid from  gd  group by gid  having count     gt  10 order by lastupdated desc

User · Answer

There can t be aggregate functions  Ex  COUNT  MAX  etc   in A WHERE clause  Hence we use the HAVING clause instead  Therefore the whole query would be similar to this   SELECT column name  aggregate function column name  FROM table name WHERE column name operator value GROUP BY column name HAVING aggregate function column name  operator value

User · Answer

SELECT COUNT    FROM  gd  GROUP BY gid HAVING COUNT gid   gt  10 ORDER BY lastupdated DESC    EDIT  if you just want the gids    SELECT MIN gid  FROM  gd  GROUP BY gid HAVING COUNT gid   gt  10 ORDER BY lastupdated DESC

User · Answer

I m not sure about what you re trying to do    maybe something like  SELECT gid  COUNT    AS num FROM gd GROUP BY gid HAVING num  gt  10 ORDER BY lastupdated DESC

User · Answer

-- searching for weather stations with missing half-hourly records  SELECT stationid FROM weather data  WHERE   Timestamp  LIKE  2011-11-15     AND  stationid IN  SELECT  ID  FROM  weather stations   GROUP BY stationid  HAVING COUNT       48    -- variation of yapiskan with a where    in    select

User · Answer

try  SELECT DISTINCT gid FROM  gd  group by gid having count     gt  10 ORDER BY max lastupdated  DESC

User · Answer

SELECT COUNT    FROM  gd  GROUP BY gid HAVING COUNT gid   gt  10 ORDER BY lastupdated DESC    EDIT  if you just want the gids    SELECT MIN gid  FROM  gd  GROUP BY gid HAVING COUNT gid   gt  10 ORDER BY lastupdated DESC

User · Answer

There can t be aggregate functions  Ex  COUNT  MAX  etc   in A WHERE clause  Hence we use the HAVING clause instead  Therefore the whole query would be similar to this   SELECT column name  aggregate function column name  FROM table name WHERE column name operator value GROUP BY column name HAVING aggregate function column name  operator value

User · Answer

SELECT COUNT    FROM  gd  GROUP BY gid HAVING COUNT gid   gt  10 ORDER BY lastupdated DESC    EDIT  if you just want the gids    SELECT MIN gid  FROM  gd  GROUP BY gid HAVING COUNT gid   gt  10 ORDER BY lastupdated DESC

User · Answer

Just academic version without having clause   select   from      select gid  count    as tmpcount from gd group by gid   as tmp where tmpcount  gt  10

User · Answer

I m not sure about what you re trying to do    maybe something like  SELECT gid  COUNT    AS num FROM gd GROUP BY gid HAVING num  gt  10 ORDER BY lastupdated DESC

User · Answer

try this   select gid from  gd  group by gid  having count     gt  10 order by lastupdated desc

User · Answer

try this   select gid from  gd  group by gid  having count     gt  10 order by lastupdated desc

User · Answer

try this   select gid from  gd  group by gid  having count     gt  10 order by lastupdated desc

User · Answer

-- searching for weather stations with missing half-hourly records  SELECT stationid FROM weather data  WHERE   Timestamp  LIKE  2011-11-15     AND  stationid IN  SELECT  ID  FROM  weather stations   GROUP BY stationid  HAVING COUNT       48    -- variation of yapiskan with a where    in    select

User · Answer

i think you can not add count   with where  now see why       where is not same as having   having means you are working or dealing with group and same work of count   it is also dealing with the whole group     now how count it is working as whole group  create a table and enter some id s and then use    select count    from table name   you will find the total values means it is indicating some group   so where does added with count

[mysql] MySQL - Using COUNT(*) in the WHERE clause

Examples related to mysql

Examples related to count

Examples related to aggregation

Examples related to having