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

Question

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

1. tbl_email_list

   1. email_list_id int (PK)
   2. email_list_name varchar

2. tbl_email-details

   1. email_uniq_id int (PK)
   2. email_list_id int (FK)
   3. email_address varchar
   4. blacklist bit

I want to retrieve data in one query which should return

1. All the email lists from tbl_email_list;
2. The total number of email_address associated with a specific email_list_id;
3. The total number of whitelisted email addresses (where blacklist=0);
4. The total number of blacklisted email addresses (where blacklist=1).

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

Answer 2

Try something like this:

SELECT 
el.emali_list_name AS EmailList
,COUNT(*) AS EmailsCount 
,SUM(CASE WHEN ed.blacklist = 1 THEN 1 ELSE 0 END) AS BlackList4ListCouint
,SUM(CASE WHEN ed.blacklist = 0 THEN 1 ELSE 0 END) AS WhiteList4ListCouint
FROM [tbl_email_list] AS el LEFT JOIN [tbl_email-details] AS ed ON (el.email_list_id = ed.email_list_id) 
GROUP BY el.emali_list_name
ORDER BY EmailList;

Please check if the query gives correct results.

I presume that you don't have 2 equal emails in the same list. Otherwise in some cases the calculations will not be correct.