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