How to split count of salespeople who had sales vs no sales?

Question

How to split count of salespeople who had sales vs no sales?

Goalie35

SSC Eights!

Points: 871
More actions
March 30, 2017 at 6:43 am

#320049

What's the cleanest way to create a query that has 2 fields: the total count of salespeople who had zero sales & the total count of salespeople who had more than zero sales WHERE the sales date equals today? The query I created works via temp tables, but I'd prefer to create a better query, if possible.
I'll be working with 2 tables: "Transactions", which contains our sales & "Employees" (which are the salespeople). Each record in this table equals 1 qty sold
dbo.Transactions table:
TransactionId | EmployeeId | TransactionDate
----------------------------------------------------------------------------
1 | 12345 | 3/22/17
2 | 67890 | 3/22/17
3 | 12345 | 3/20/17
4 | 67890 | 3/22/17
dbo.Employees table:
EmployeeId | Name
----------------------------------------------------
12345 | John Smith
67890 | Amy Andrews
99999 | Paul Jones
So, in the above, on 3/22/17, I have 1 employee with 2 sales. 1 with 1 sale. And 1 with no sales. I would like my results to look like the following, if I were to run this query on 3/22/17:
Total Employees with Sales | Total Employees with No Sales
----------------------------------------------------------------------------------------------------------
2 | 1
What's the cleanest way to do this?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99134 More actions · Answer 1

No need for temporary tables, you can do this with a simple aggregate and CASE statement:
SELECT COUNT(DISTINCT CASE WHEN T.TransactionId IS NOT NULL THEN E.EmployeeId ELSE NULL END) AS EmployeesWithSales, COUNT(DISTINCT CASE WHEN T.TransactionId IS NULL THEN E.EmployeeId ELSE NULL END) AS EmployeesWithoutSales FROM dbo.Employees E LEFT JOIN dbo.Transactions T on E.EmployeeId = T.EmployeeId WHERE T.TransactionDate = CAST(GETDATE() AS date);

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246993 More actions · Answer 2

Thom A - Thursday, March 30, 2017 6:50 AM
No need for temporary tables, you can do this with a simple aggregate and CASE statement:
SELECT COUNT(DISTINCT CASE WHEN T.TransactionId IS NOT NULL THEN E.EmployeeId ELSE NULL END) AS EmployeesWithSales, COUNT(DISTINCT CASE WHEN T.TransactionId IS NULL THEN E.EmployeeId ELSE NULL END) AS EmployeesWithoutSales FROM dbo.Employees E LEFT JOIN dbo.Transactions T on E.EmployeeId = T.EmployeeId WHERE T.TransactionDate = CAST(GETDATE() AS date);

You might need to add
OR T.TransactionDate is NULL
to your predicate, or the LEFT JOIN is pointless.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99134 More actions · Answer 3

Phil Parkin - Thursday, March 30, 2017 7:38 AM
Thom A - Thursday, March 30, 2017 6:50 AM
No nYou're rgeed for temporary tables, you can do this with a simple aggregate and CASE statement:
SELECT COUNT(DISTINCT CASE WHEN T.TransactionId IS NOT NULL THEN E.EmployeeId ELSE NULL END) AS EmployeesWithSales, COUNT(DISTINCT CASE WHEN T.TransactionId IS NULL THEN E.EmployeeId ELSE NULL END) AS EmployeesWithoutSales FROM dbo.Employees E LEFT JOIN dbo.Transactions T on E.EmployeeId = T.EmployeeId WHERE T.TransactionDate = CAST(GETDATE() AS date) OR T.TransactionDate IS NULL;
You might need to add
OR T.TransactionDate is NULL
to your predicate, or the LEFT JOIN is pointless.

You're so very right (as always), Phil. My brain has been less than engaged all day today (been dealing with my property purchasing instead, lol). Corrected.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246993 More actions · Answer 4

Thom A - Thursday, March 30, 2017 7:43 AM
You're so very right (as always), Phil. My brain has been less than engaged all day today (been dealing with my property purchasing instead, lol). Corrected.

Not so sure about the 'as always', but thanks🙂 Property purchasing is definitely more worthy of your attention, good luck.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel