SQL Query subquery with COUNT()

Question

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.

The subquery will do.

juergen d
– juergen d

2020-06-14 06:55:55 +00:00
Commented Jun 14, 2020 at 6:55 — juergen d
– juergen d, Commented Jun 14, 2020 at 6:55

zealous · Accepted Answer · 2020-06-14 06:55:53Z

3

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

answered Jun 14, 2020 at 6:55

zealous

7,5434 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dsk · Accepted Answer · 2020-06-14 07:11:44Z

0

SELECT *
FROM
(
SELECT conm, COUNT(conm) AS 'Number_of_Rows'
FROM combinedata1 
GROUP BY conm
)
WHERE Number_of_Rows = 79;

edited Jun 14, 2020 at 7:11

answered Jun 14, 2020 at 6:57

dsk

2,0272 gold badges12 silver badges13 bronze badges

1 Comment

ysf Over a year ago

While this code may resolve the OP's issue, it is best to include an explanation as to how your code addresses the OP's issue. In this way, future visitors can learn from your post, and apply it to their own code. SO is not a coding service, but a resource for knowledge. Also, high quality, complete answers are more likely to be upvoted. These features, along with the requirement that all posts are self-contained, are some of the strengths of SO as a platform, that differentiates it from forums. You can edit to add additional info &/or to supplement your explanations with source documentation.

Gordon Linoff · Accepted Answer · 2020-06-14 11:27:17Z

0

First, your query is returning everything because that is how EXISTS work. The subquery is not related to the outer query. So, either all rows are returned (if the subquery returns any rows) or no rows are returned.

You can fix this with a correlated subquery. However window functions are faster and simpler to query:

SELECT c.*
FROM (SELECT c.*, COUNT(*) OVER (PARTITION BY conm) as cnt
      FROM combinedata1 c
     ) c
WHERE cnt = 79;
ORDER BY conm ASC;

answered Jun 14, 2020 at 11:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Dom · Accepted Answer · 2020-06-14 16:58:24Z

0

I find that it is simpler to express with a scalar subquery than with exists:

select c.*
from combinedata1 c
where (select count(*) from combinedata1 where c1.conm = c.conm) = 79

The subquery just count how many rows with the same conm can be found in the table: this returns a scalar value (the count), which you can directly filter on. This query would take advantage of an index on conm.

You could also use window functions:

select *
from (select c.*, count(*) over(partition by conm) cnt from combinedata1 c) c
where cnt = 79

edited Jun 14, 2020 at 16:58

Dom

901 gold badge3 silver badges11 bronze badges

answered Jun 14, 2020 at 9:13

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

SQL Query subquery with COUNT()

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related