I am trying to accomplish the following in MySQL (see pseudo code)
SELECT DISTINCT gid
FROM `gd`
WHERE COUNT(*) > 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.
This question is related to
mysql
count
aggregation
having
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() ;
try this;
select gid
from `gd`
group by gid
having count(*) > 10
order by lastupdated desc
SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC;
EDIT (if you just want the gids):
SELECT MIN(gid)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC
Just academic version without having clause:
select *
from (
select gid, count(*) as tmpcount from gd group by gid
) as tmp
where tmpcount > 10;
try this;
select gid
from `gd`
group by gid
having count(*) > 10
order by lastupdated desc
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;
try
SELECT DISTINCT gid
FROM `gd`
group by gid
having count(*) > 10
ORDER BY max(lastupdated) DESC
-- 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
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;
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(*) > 10
ORDER BY COUNT(*) ASC
try this;
select gid
from `gd`
group by gid
having count(*) > 10
order by lastupdated desc
SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC;
EDIT (if you just want the gids):
SELECT MIN(gid)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC
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 > 10 ORDER BY lastupdated DESC
try this;
select gid
from `gd`
group by gid
having count(*) > 10
order by lastupdated desc
SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC;
EDIT (if you just want the gids):
SELECT MIN(gid)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC
try
SELECT DISTINCT gid
FROM `gd`
group by gid
having count(*) > 10
ORDER BY max(lastupdated) DESC
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 > 10 ORDER BY lastupdated DESC
Just academic version without having clause:
select *
from (
select gid, count(*) as tmpcount from gd group by gid
) as tmp
where tmpcount > 10;
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 > 10 ORDER BY lastupdated DESC
SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC;
EDIT (if you just want the gids):
SELECT MIN(gid)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC
try
SELECT DISTINCT gid
FROM `gd`
group by gid
having count(*) > 10
ORDER BY max(lastupdated) DESC
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(*) > 10
ORDER BY COUNT(*) ASC
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 > 10 ORDER BY lastupdated DESC
try
SELECT DISTINCT gid
FROM `gd`
group by gid
having count(*) > 10
ORDER BY max(lastupdated) DESC
Source: Stackoverflow.com