Any way to have multiple counts within same SQL query?

Question

What is the most elegant/efficient way to check multiple different count()'s with different conditions within the same statement? Given the syntax of SQL, it appears that I can only have one count() statement with each set of WHERE/HAVING conditionals within my query. Therefore, to utilize a different set of WHERE/HAVING conditionals, it seems this second count(*) needs to be in a different select statement. Is the best way to put this into the same result set to UNION them? Is there any alternative?
Ideally I could do something like:

SELECT count(*) as First, count(*) as Second  
FROM table  
WHERE --conditionals for First  
AND  
--conditionals for Second

But I think this is impossible. Is the only way to do it to UNION two separate queries?

What you want here is a case expression. SELECT COUNT(CASE WHEN //conditionals THEN 1 END), COUNT(CASE WHEN //conditionals THEN 1 END) as Second FROM table; — JNevill
– JNevill, Commented Aug 24, 2021 at 15:56
You would use conditional aggregation where COUNT is implemented as SUM(CASE WHEN ... 1 ELSE 0 END). — PM 77-1
– PM 77-1, Commented Aug 24, 2021 at 15:58
You can combine conditional aggregation (SUM+CASE) with window frames (OVER). — The Impaler
– The Impaler, Commented Aug 24, 2021 at 15:59

Gordon Linoff · Accepted Answer · 2021-08-24 16:00:18Z

4

The canonical method uses case with an aggregation function:

select sum(case when condition1 then 1 else 0 end),
       sum(case when condition2 then 1 else 0 end)

The SQL Standard method uses filter:

select count(*) filter (where condition1),
       count(*) filter (where condition2)

answered Aug 24, 2021 at 16:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Gravitate · Accepted Answer · 2021-08-24 16:00:32Z

1

It might not be the most efficient but you could use sub-queries:

SELECT
    (SELECT Count (*) FROM table WHERE <conditionals for First> ) As First,
    (SELECT Count (*) FROM table WHERE <conditionals for Second>) As Second

answered Aug 24, 2021 at 16:00

Gravitate

3,0722 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

Any way to have multiple counts within same SQL query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related