How to get multiple counts with one SQL query?

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.

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.

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

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.