SQL Group by Sales Rep - Select 2 counts-CodePudding

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	[email protected]	Jeans	1
1	[email protected]	Hat	1
1	[email protected]	Shoes	3
2	[email protected]	Jeans	1
2	[email protected]	Socks	1
2	[email protected]	Hat	1
4	[email protected]	Shirt	3
5	[email protected]	Hat	3
5	[email protected]	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.

CodePudding user response：

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