How can I get multiple counts with one SQL query?

Question

I am wondering how to write this query.

I know this actual syntax is bogus, but it will help you understand what I want.

I need it in this format, because it is part of a much bigger query.

SELECT distributor_id,
COUNT(*) AS TOTAL,
COUNT(*) WHERE level = 'exec',
COUNT(*) WHERE level = 'personal'

I need this all returned in one query.

Also, it need to be in one row, so the following won't work:

'SELECT distributor_id, COUNT(*)
GROUP BY distributor_id'

Did this query of you worked properly ?? SELECT distributor_id, COUNT(*) AS TOTAL, COUNT(*) WHERE level = 'exec', COUNT(*) WHERE level = 'personal' — Pratik Joshi, Commented Mar 13, 2016 at 8:04

Chad · Accepted Answer · 2019-09-23 14:01:08Z

1064

You can use a CASE statement with an aggregate function. This is basically the same thing as a PIVOT function in some RDBMS:

SELECT distributor_id,
    count(*) AS total,
    sum(case when level = 'exec' then 1 else 0 end) AS ExecCount,
    sum(case when level = 'personal' then 1 else 0 end) AS PersonalCount
FROM yourtable
GROUP BY distributor_id

edited Sep 23, 2019 at 14:01

Chad

1,1991 gold badge17 silver badges42 bronze badges

answered Oct 8, 2012 at 21:07

Taryn

248k57 gold badges371 silver badges408 bronze badges

83

Fantastic, this is amazing. Great answer. Just a note to people who have stumbled here. Count will count all rows, the sum will do the same thing as a count when used with a case statement.
– John Ballinger
Commented Jun 1, 2014 at 20:46
1

Brilliant solution! It's probably worth noting that this method works just as nicely if you're combining lots of tables together in one query, as using sub-queries can get quite messy in that instance.
– Darren Crabb
Commented Oct 8, 2015 at 11:24
8

Thanks for this very elegant solution. Btw, this also works with TSQL.
– Annie Lagang
Commented Sep 9, 2016 at 0:52
7

Why this might not be the best answer: always a full table scan. Consider a join of count-subqueries, or nested counts in a select. However with no indexes present, this might be best as you have guaranteed only one table scan vs multiple. See answer from @KevinBalmforth
– YoYo
Commented Apr 1, 2017 at 19:20
2

With Postgres 9.4 or newer you can use a FILTER clause which achieves the same result with cleaner syntax: COUNT(*) FILTER (WHERE level = 'exec') AS ExecCount
– xthrd
Commented Nov 25, 2020 at 4:10

| Show 9 more comments

Community · Accepted Answer · 2020-03-03 04:20:22Z

125

One way which works for sure

SELECT a.distributor_id,
    (SELECT COUNT(*) FROM myTable WHERE level='personal' and distributor_id = a.distributor_id) as PersonalCount,
    (SELECT COUNT(*) FROM myTable WHERE level='exec' and distributor_id = a.distributor_id) as ExecCount,
    (SELECT COUNT(*) FROM myTable WHERE distributor_id = a.distributor_id) as TotalCount
FROM (SELECT DISTINCT distributor_id FROM myTable) a ;

EDIT:
See @KevinBalmforth's break down of performance for why you likely don't want to use this method and instead should opt for @Taryn♦'s answer. I'm leaving this so people can understand their options.

edited Mar 3, 2020 at 4:20

CommunityBot

11 silver badge

answered Oct 8, 2012 at 21:03

ChrisLively

88.1k27 gold badges173 silver badges248 bronze badges

3

This helped me resolve how to do multiple counts and output them in a single SELECT statement with each count being a column. Works great--thanks!
– Mark
Commented Jan 22, 2016 at 21:18
2

I was able to use what you provided here, in a project of mine. Now everything is in a single Query, instead of multiple queries. The page loads in less than a second, compared to 5-8 seconds with multiple queries. Love it. Thanks, Notme.
– Wayne Barron
Commented Mar 13, 2017 at 2:42
2

This might work well if each sub query actually hits an index. If not, then sum(case...) solution should be considered.
– YoYo
Commented Apr 1, 2017 at 19:23
2

Note that as an alternative to distinct, as I have made the correction, you can also/better use group by with the benefit of replacing an entire nested query with a simple count(*) as @Mihai shows - with further MySQL only syntax simplifications.
– YoYo
Commented Apr 1, 2017 at 19:29
1

@NobleUplift those answers are literally on this same web page. If people cant figure that out then I doubt they’d anything other than just copy/paste the top voted answer anyway.
– ChrisLively
Commented Jun 6, 2023 at 20:57

| Show 1 more comment

Majid Laissi · Accepted Answer · 2012-10-08 21:10:23Z

53

SELECT 
    distributor_id, 
    COUNT(*) AS TOTAL, 
    COUNT(IF(level='exec',1,null)),
    COUNT(IF(level='personal',1,null))
FROM sometable;

COUNT only counts non null values and the DECODE will return non null value 1 only if your condition is satisfied.

edited Oct 8, 2012 at 21:10

answered Oct 8, 2012 at 21:05

Majid Laissi

19.8k20 gold badges71 silver badges107 bronze badges

which distributor_id will the query show ? It shows 1 row in total.
– Istiaque Ahmed
Commented Nov 8, 2017 at 18:20
The OP has a group by on the column that was omitted in my answer.
– Majid Laissi
Commented Nov 8, 2017 at 18:25

Add a comment |

Kevin Balmforth · Accepted Answer · 2015-12-03 16:29:50Z

Building on other posted answers.

Both of these will produce the right values:

select distributor_id,
    count(*) total,
    sum(case when level = 'exec' then 1 else 0 end) ExecCount,
    sum(case when level = 'personal' then 1 else 0 end) PersonalCount
from yourtable
group by distributor_id

SELECT a.distributor_id,
          (SELECT COUNT(*) FROM myTable WHERE level='personal' and distributor_id = a.distributor_id) as PersonalCount,
          (SELECT COUNT(*) FROM myTable WHERE level='exec' and distributor_id = a.distributor_id) as ExecCount,
          (SELECT COUNT(*) FROM myTable WHERE distributor_id = a.distributor_id) as TotalCount
       FROM myTable a ;

However, the performance is quite different, which will obviously be more relevant as the quantity of data grows.

I found that, assuming no indexes were defined on the table, the query using the SUMs would do a single table scan, while the query with the COUNTs would do multiple table scans.

As an example, run the following script:

IF OBJECT_ID (N't1', N'U') IS NOT NULL 
drop table t1

create table t1 (f1 int)


    insert into t1 values (1) 
    insert into t1 values (1) 
    insert into t1 values (2)
    insert into t1 values (2)
    insert into t1 values (2)
    insert into t1 values (3)
    insert into t1 values (3)
    insert into t1 values (3)
    insert into t1 values (3)
    insert into t1 values (4)
    insert into t1 values (4)
    insert into t1 values (4)
    insert into t1 values (4)
    insert into t1 values (4)


SELECT SUM(CASE WHEN f1 = 1 THEN 1 else 0 end),
SUM(CASE WHEN f1 = 2 THEN 1 else 0 end),
SUM(CASE WHEN f1 = 3 THEN 1 else 0 end),
SUM(CASE WHEN f1 = 4 THEN 1 else 0 end)
from t1

SELECT 
(select COUNT(*) from t1 where f1 = 1),
(select COUNT(*) from t1 where f1 = 2),
(select COUNT(*) from t1 where f1 = 3),
(select COUNT(*) from t1 where f1 = 4)

Highlight the 2 SELECT statements and click on the Display Estimated Execution Plan icon. You will see that the first statement will do one table scan and the second will do 4. Obviously one table scan is better than 4.

Adding a clustered index is also interesting. E.g.

Create clustered index t1f1 on t1(f1);
Update Statistics t1;

The first SELECT above will do a single Clustered Index Scan. The second SELECT will do 4 Clustered Index Seeks, but they are still more expensive than a single Clustered Index Scan. I tried the same thing on a table with 8 million rows and the second SELECT was still a lot more expensive.

HoldOffHunger · Accepted Answer · 2020-02-21 18:58:09Z

33

For MySQL, this can be shortened to:

SELECT distributor_id,
    COUNT(*) total,
    SUM(level = 'exec') ExecCount,
    SUM(level = 'personal') PersonalCount
FROM yourtable
GROUP BY distributor_id

edited Feb 21, 2020 at 18:58

HoldOffHunger

21k11 gold badges120 silver badges146 bronze badges

answered Dec 3, 2015 at 16:12

Mihai

26.8k8 gold badges69 silver badges86 bronze badges

2

was "group by distributor_id""really necessary in this query? It can work without that as well
– user1451111
Commented Nov 27, 2017 at 7:20
2

@user1451111 original question got it so it is answer depends on the question itself
– Al-Mothafar
Commented Oct 9, 2018 at 9:49

Add a comment |

CrazyCasta · Accepted Answer · 2012-10-08 21:03:50Z

11

Well, if you must have it all in one query, you could do a union:

SELECT distributor_id, COUNT() FROM ... UNION
SELECT COUNT() AS EXEC_COUNT FROM ... WHERE level = 'exec' UNION
SELECT COUNT(*) AS PERSONAL_COUNT FROM ... WHERE level = 'personal';

Or, if you can do after processing:

SELECT distributor_id, COUNT(*) FROM ... GROUP BY level;

You will get the count for each level and need to sum them all up to get the total.

answered Oct 8, 2012 at 21:03

CrazyCasta

28.4k4 gold badges49 silver badges73 bronze badges

Found UNION to be very helpful when generating a report containing multiple instances of the COUNT(*) function.
– James O
Commented Sep 22, 2015 at 20:48
The result shows #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') FROM distributors UNION SELECT COUNT() AS EXEC_COUNT FROM distributors WHERE ' at line 1.
– Istiaque Ahmed
Commented Nov 8, 2017 at 16:47
number of columns returned from all queries, on which a UNION is applied, should be equal. @IstiaqueAhmed probably that is the reason behind your error.
– user1451111
Commented Jun 10, 2018 at 13:25
A note for anyone who stumble upon this answer in future. The 'After Processing' technique described here may cause issue when some of the values in 'level' columns are NULL. In that case the sum of all the sub-counts will not be equal to the total row count.
– user1451111
Commented Jun 10, 2018 at 13:55

Add a comment |

Frantumn · Accepted Answer · 2017-09-20 19:07:36Z

8

I do something like this where I just give each table a string name to identify it in column A, and a count for column. Then I union them all so they stack. The result is pretty in my opinion - not sure how efficient it is compared to other options but it got me what I needed.

select 'table1', count (*) from table1
union select 'table2', count (*) from table2
union select 'table3', count (*) from table3
union select 'table4', count (*) from table4
union select 'table5', count (*) from table5
union select 'table6', count (*) from table6
union select 'table7', count (*) from table7;

Result:

-------------------
| String  | Count |
-------------------
| table1  | 123   |
| table2  | 234   |
| table3  | 345   |
| table4  | 456   |
| table5  | 567   |
-------------------

answered Sep 20, 2017 at 19:07

Frantumn

1,7747 gold badges38 silver badges64 bronze badges

1

a query that I created makes ...- where is that query ?
– Istiaque Ahmed
Commented Nov 10, 2017 at 12:30
3

how to add where caluse to all tables
– user9260670
Commented Apr 29, 2018 at 6:53

Add a comment |

Peter Mortensen · Accepted Answer · 2023-02-07 03:40:38Z

7

Based on Taryn's response with an added nuance using OVER():

SELECT distributor_id,
    COUNT(*) total,
    SUM(case when level = 'exec' then 1 else 0 end) OVER() ExecCount,
    SUM(case when level = 'personal' then 1 else 0 end) OVER () PersonalCount
FROM yourtable
GROUP BY distributor_id

Using OVER() with nothing in the () will give you the total count for the whole dataset.

edited Feb 7, 2023 at 3:40

Peter Mortensen

31.6k22 gold badges110 silver badges133 bronze badges

answered Nov 9, 2017 at 19:15

mentorrory

711 silver badge1 bronze badge

this "added nuance" is also useful in a common table expression (cte)
– wistlo
Commented Mar 19, 2022 at 15:19

Add a comment |

Domingos Manuel · Accepted Answer · 2022-03-04 12:53:57Z

2

In Oracle you'll do something like

SELECT
    (SELECT COUNT(*) FROM schema.table1),
    (SELECT COUNT(*) FROM schema.table2),
    ...
    (SELECT COUNT(*) FROM schema.tableN)
FROM DUAL;

answered Mar 4, 2022 at 12:53

Domingos Manuel

593 bronze badges

Add a comment |

Stevoisiak · Accepted Answer · 2022-04-29 15:47:59Z

2

If your flavor of SQL supports it, you can use COUNT_IF() to count based on a condition.

SELECT
    distributor_id, 
    COUNT(*) AS total_count, 
    COUNT_IF(level = 'exec') AS exec_count, 
    COUNT_IF(level = 'personal') AS personal_count
FROM table_name
GROUP BY distributor_id

answered Apr 29, 2022 at 15:47

Stevoisiak

27.1k32 gold badges136 silver badges239 bronze badges

Add a comment |

Sinte · Accepted Answer · 2018-04-13 07:10:58Z

1

I think this can also works for you select count(*) as anc,(select count(*) from Patient where sex='F')as patientF,(select count(*) from Patient where sex='M') as patientM from anc

and also you can select and count related tables like this select count(*) as anc,(select count(*) from Patient where Patient.Id=anc.PatientId)as patientF,(select count(*) from Patient where sex='M') as patientM from anc

answered Apr 13, 2018 at 7:10

Sinte

911 silver badge10 bronze badges

Add a comment |

simon · Accepted Answer · 2022-07-18 15:53:02Z

0

The recently added PIVOT functionality can do exactly what you need:

SELECT *
FROM ( SELECT level from your_table )
PIVOT ( count(*) for level in ('exec', 'personal') )

answered Jul 18, 2022 at 15:53

simon

7001 gold badge4 silver badges15 bronze badges

Add a comment |

Collectives™ on Stack Overflow

How can I get multiple counts with one SQL query?

12 Answers 12

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

Linked

Related