How to 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 am wanting. 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'

Solution 1

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

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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

View more solutions

Comments

[John Ballinger]

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.

[James O]

Found UNION to be very helpful when generating a report containing multiple instances of the COUNT(*) function.

[Darren Crabb]

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.

[Mark]

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!

[Annie Lagang]

Thanks for this very elegant solution. Btw, this also works with TSQL.

[Wayne Barron]

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.

[YoYo]

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]

This might work well if each sub query actually hits an index. If not, then sum(case...) solution should be considered.

[YoYo]

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.

[Istiaque Ahmed]

@JohnBallinger, 'Count will count all rows' - COUNT will count distributor_id wise . not all the rows of the table, right ?

[Taryn]

@IstiaqueAhmed Yes, that is correct, it will count by distributor_id since the is the grouping column.

[Istiaque Ahmed]

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]

which distributor_id will the query show ? It shows 1 row in total.

[Majid Laissi]

The OP has a group by on the column that was omitted in my answer.

[Istiaque Ahmed]

a query that I created makes ...- where is that query ?

[user1451111]

was "group by distributor_id""really necessary in this query? It can work without that as well

[user1451111]

was "group by distributor_id""really necessary in this query? It can work without that as well

[Taryn]

@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. I'd rather be specific and be sure that it's returning what I'd expect each time.

[Admin]

how to add where caluse to all tables

[user1451111]

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]

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]

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.

[Taryn]

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

[Al-Mothafar]

@user1451111 original question got it so it is answer depends on the question itself

[Frédéric Nobre]

Amazing that was EXACTLY what i needed ! Thanks !

[Abner]

you saved my life, all the other aswers return multiple rows in MySQL. Thanks so much

[Majid Laissi]

@Abner glad this still helps after 8 years :)

[Abner]

@MajidLaissi yes it did, changed my query time from a minute to less than a second. :)

[daticon]

Yes, Saved my life this query ! THANKS!

[xthrd]

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

[funder7]

Fantastic! Probably this is the second time that I use your answer :-)

[Tạ Anh Tú]

Your solution saved my day! Thanks!

[Joel]

amazing indeed. after wasting some time with my developer I googled and found this lovely solution. should have done this right away!

[Leponzo]

Using count instead of sum is shorter: count(case when level = 'exec' then 1 end).

[wistlo]

this "added nuance" is also useful in a common table expression (cte)

[Doreen]

I learned something new... years later...thank you this is what I love about this forum.