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'

Answer 1

You can use aggregate function with the CASE Statement. As it’s similar to PIVOT function in RDBMS:

You can refer to this video to learn CASE STATEMENT in SQL server:

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

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL course.

Answer 2

To get multiple counts in a single SQL query, use COUNT() with GROUP BY or you can use conditional aggregation by summing up with SUM() and CASE expressions. Here are two ways:
Method 1:
SELECT col_name, COUNT(*) AS count
FROM table_name
GROUP BY col_name;

Method 2:
SELECT
    COUNT(CASE WHEN condition1 THEN 1 END) AS count1,
    COUNT(CASE WHEN condition2 THEN 1 END) AS count2,
    COUNT(CASE WHEN condition3 THEN 1 END) AS count3
FROM table_name;