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

2 Answers 2

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


Try something like this:

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.