Join two tables and return data and count in a single query

I have two tables in an SQL Server database, one with two columns and one with four:

-
tbl_email_list

• email_list_id int (PK)

• email_list_name varchar

-
tbl_email-details

• email_uniq_id int (PK)

• email_list_id int (FK)

• email_address varchar

• blacklist bit

I want to retrieve data in one query which should return

• All the email lists from tbl_email_list;

• The total number of email_address associated with a specific email_list_id;

• The total number of whitelisted email addresses (where blacklist=0);

• The total number of blacklisted email addresses (where blacklist=1).

select l.email_list_id, l.email_list_name,
    count(d.email_uniq_id) as full_count,
    count(case when d.blacklist = 0 then d.email_uniq_id end) as white_count,
    count(case when d.blacklist = 1 then d.email_uniq_id end) as black_count
from tbl_email_list as l
left join [tbl_email-details] as d on d.email_list_id = l.email_list_id
group by l.email_list_id, l.email_list_name;

By counting things which can be null, we let zeroes appear in the result set, which is a very useful technique. Here I do it for both the blacklist results and the overall (based on the outer join).

StackExchange Database Administrators Q#98663, answer score: 7