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.