SQL Group by Sales Rep - Select 2 counts

user_476061 0
Viewed 56

I would like to query a table that has leads assigned by sales rep to return the unique number of leads grouped by agent and also the number sold. There can be multiple leads from one buyer, I would like to select distinct so each buyer is counted only once. Here is the layout of the data:

AgentId BuyerEmail Product Category
1 lisa@gmail.com Jeans 1
1 lisa@gmail.com Hat 1
1 ryan@gmail.com Shoes 3
2 mark@gmail.com Jeans 1
2 mark@gmail.com Socks 1
2 mark@gmail.com Hat 1
4 john@gmail.com Shirt 3
5 lou@gmail.com Hat 3
5 tim@gmail.com Shirt 3

I would like to return a dataset like the following:

AgentId UniqueLeads QtySold
1 2 1
2 1 0
4 1 1
5 2 2

I can query this individually but I can't get it to return in one result set. Here are the 2 separate queries:

SELECT COUNT(DISTINCT BuyerEmail) FROM SalesLeads GROUP BY InitialAgent 

SELECT COUNT(DISTINCT BuyerEmail) FROM SalesLeads WHERE Category = 3 GROUP BY InitialAgent

How can I query the table and have both data points return in one result set? Please note, a category = 3 means it is sold.

sql-server sql
1 Answers

You can use conditional aggregation to calculate QtySold in the same statement:

select AgentId, 
    count(distinct BuyerEmail) as UniqueLeads, 
    count(case when Category = 3 then Category end) as QtySold
from SalesLeads
group by AgentId

When Category is anything other than 3 the case statement returns null so that record isn't counted in the QtySold calculation.

db<>fiddle

user_8681700
Related