SQL Query subquery with COUNT()

I’m trying to return the ‘conm’ that has 79 rows only.

Here is my query:

SELECT *
FROM combinedata1 
WHERE EXISTS (    
    SELECT conm, COUNT(conm) AS 'Number of Rows'
    FROM combinedata1 
    GROUP BY conm
    HAVING COUNT(conm) = 79
)
ORDER BY conm ASC

Apparently, this is returning almost everything, ignoring the conm with 79 rows.

Can anyone shed some light on this?

Thanks.

Answer

You should have where inside exists as following

SELECT *
FROM combinedata1 c1
WHERE EXISTS
(
    SELECT c2.conm, COUNT(c2.conm) AS 'Number of Rows'
    FROM combinedata1 c2
    WHERE c1.conm = c2.conm
    GROUP BY c2.conm
    HAVING COUNT(c2.conm) = 79
)
ORDER BY conm ASC

Advertisement

Answer