How to get the correct sum of two columns using case when

Question

I'm developing a program that will track the units sold by a salesperson, which can be either a full deal (1) or a half deal (0.5). What I need to do is find a way to get the sum of all transactions and half of the transactions grouped by salesperson ID.

This is the database structure:

id	salesperson_id	salesperson_two_id	Sold Date
1	5	null	2022-07-02
2	3	5	2022-07-18
3	4	null	2022-07-16
4	5	3	2022-07-12
5	3	5	2022-07-17
6	5	null	2022-07-18

If I want to retrieve only one salesperson's SUM, I have a valid query:

SELECT
SUM(case when salesperson_id = 5 and isnull(salesperson_two_id) then 1 end) as fullDeals,
SUM(case when salesperson_id != 5 and salesperson_two_id = 5
or salesperson_id = 5 and salesperson_two_id != 5 then 0.5 end) as halfDeals
FROM sold_logs WHERE MONTH(sold_date) = 07 AND YEAR(sold_date) = 2022;

The output will be as expected:

All transactions	HALF TRANSACTION
2	1.5

What I want to accomplish is to get these results for all salespeople in the table but don't know how to achieve it. This is the result I want to get:

salesperson_id	Total Transaction
5	3.5
3	1.5
4	1

If possible, I would like the results to be sorted by total number of transactions.

Answer 1

Use UNION ALL to get a result set containing all rows for each salesperson, filter out the months you want and aggregate:

SELECT salesperson_id,
       SUM(CASE WHEN salesperson_two_id IS NULL THEN 1 ELSE 0.5 END) totalDeals
FROM (
  SELECT salesperson_id, salesperson_two_id, sold_date FROM sold_logs
  UNION ALL
  SELECT salesperson_two_id, salesperson_id, sold_date FROM sold_logs WHERE salesperson_two_id IS NOT NULL
) t
WHERE MONTH(sold_date) = 7 AND YEAR(sold_date) = 2022
GROUP BY salesperson_id
ORDER BY totalDeals DESC;

ViewDemo.