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, Mar 13 '16 at 08:04

score 1028 · Accepted Answer · edited Sep 23 '19 at 14:01

1028

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 '19 at 14:01

Chad

1,139
17
41

answered Oct 08 '12 at 21:07

Taryn

242,637
56
362
405

78

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 Jun 01 '14 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 Oct 08 '15 at 11:24
8

Thanks for this very elegant solution. Btw, this also works with TSQL. – Annie Lagang Sep 09 '16 at 00: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 Apr 01 '17 at 19:20
1

@JohnBallinger, 'Count will count all rows' - `COUNT` will count `distributor_id` wise . not all the rows of the table, right ? – Istiaque Ahmed Nov 08 '17 at 16:37
@IstiaqueAhmed Yes, that is correct, it will count by `distributor_id` since the is the grouping column. – Taryn Nov 08 '17 at 16:39
was "group by distributor_id""really necessary in this query? It can work without that as well – user1451111 Nov 27 '17 at 07:17
@user1451111 If you don't include the `distributor_id`, then you're not telling the engine exactly what you want. While the query might work without it, MySQL could return an incorrect value for the `distributor_id`, see [how MySQL handles Group By](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html). I'd rather be specific and be sure that it's returning what I'd expect each time. – Taryn Nov 27 '17 at 13:59
I tried the query in MS SQL Server. Your mentioned query (in the answer) works perfect without the `GROUP BY` as far as the OP's situation and intended answer is concerned. Adding `GROUP BY` at the end generates two rows split by groups and does not show the total number of rows.in the table. But again, I can't verify it for MySQL. – user1451111 Jun 10 '18 at 14:01
1

@user1451111 what do you mean it works in SQL server without a Group By? SQL server requires group by when you are including a column outside of an aggregate. – Taryn Jun 11 '18 at 00:41
1

With Postgres 9.4 or newer you can use a [FILTER clause](https://www.postgresql.org/docs/9.6/sql-expressions.html#SYNTAX-AGGREGATES) which achieves the same result with cleaner syntax: `COUNT(*) FILTER (WHERE level = 'exec') AS ExecCount` – xthrd Nov 25 '20 at 04:10
1

Using `count` instead of `sum` is shorter: `count(case when level = 'exec' then 1 end)`. – Leponzo Sep 01 '21 at 01:22
worked but as suggested by @xthrd filter can be an alternate approach to this – Abu Talha Siddiqi Jan 13 '23 at 10:25

score 123 · Answer 2 · edited Mar 03 '20 at 04:20

123

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 03 '20 at 04:20

Community

1
1

answered Oct 08 '12 at 21:03

NotMe

87,343
27
171
245

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 Jan 22 '16 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 Mar 13 '17 at 02:42
2

This might work well if each sub query actually hits an index. If not, then `sum(case...)` solution should be considered. – YoYo Apr 01 '17 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 Apr 01 '17 at 19:29
Could you add links to KevinBalmforth and Taryn's answers so that the edit is more apparent? https://stackoverflow.com/a/12789493/904344 – NobleUplift Mar 14 '23 at 14:27
@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. – NotMe Jun 06 '23 at 20:57

Majid Laissi · Answer 3 · 2012-10-08T21:10:23.737

51

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 08 '12 at 21:10

answered Oct 08 '12 at 21:05

Majid Laissi

19,188
19
68
105

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

you saved my life, all the other aswers return multiple rows in MySQL. Thanks so much – Abner Apr 06 '20 at 15:05
3

@Abner glad this still helps after 8 years :) – Majid Laissi Apr 06 '20 at 16:24
1

@MajidLaissi yes it did, changed my query time from a minute to less than a second. :) – Abner Apr 07 '20 at 03:39

Kevin Balmforth · Answer 4 · 2015-12-03T16:29:50.767

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.

score 32 · Answer 5 · edited Feb 21 '20 at 18:58

32

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 '20 at 18:58

HoldOffHunger

18,769
10
104
133

answered Dec 03 '15 at 16:12

Mihai

26,325
7
66
81

2

was "group by distributor_id""really necessary in this query? It can work without that as well – user1451111 Nov 27 '17 at 07:20
2

@user1451111 original question got it so it is answer depends on the question itself – Al-Mothafar Oct 09 '18 at 09:49

score 11 · Answer 6 · answered Oct 08 '12 at 21:03

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 08 '12 at 21:03

CrazyCasta

26,917
4
45
72

Found `UNION` to be very helpful when generating a report containing multiple instances of the `COUNT(*)` function. – James O Sep 22 '15 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 Nov 08 '17 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 Jun 10 '18 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 Jun 10 '18 at 13:55

score 8 · Answer 7 · answered Sep 20 '17 at 19:07

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   |
-------------------

`a query that I created makes ...`- where is that query ? – Istiaque Ahmed Nov 10 '17 at 12:30 — Istiaque Ahmed, Nov 10 '17 at 12:30
how to add where caluse to all tables – Apr 29 '18 at 06:53 — , Apr 29 '18 at 06:53

score 7 · Answer 8 · edited Feb 07 '23 at 03:40

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 07 '23 at 03:40

Peter Mortensen

30,738
21
105
131

answered Nov 09 '17 at 19:15

mentorrory

71
1
1

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

score 1 · Answer 9 · answered Apr 13 '18 at 07:10

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

score 1 · Answer 10 · answered Mar 04 '22 at 12:53

1

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 04 '22 at 12:53

Domingos Manuel

39
3

score 1 · Answer 11 · answered Apr 29 '22 at 15:47

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

score 0 · Answer 12 · answered Jul 18 '22 at 15:53

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 '22 at 15:53

simon

615
4
13

How can I get multiple counts with one SQL query?

12 Answers12

Linked

Related