I am working on a program that will track a salespersons sold units, these units can be full deals (1) or half deals (0.5). What I need to do is find a way to get the SUM of the full deals and the half deals grouped by a salespersons ID.

Here is the database structure:

I have a query that works if I only want to retrieve the SUM for one salesperson:

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;

Output would be as expected:

What I am trying to accomplish is get these results for all salespeople in the table and have no clue how to make it happen. Here is what I am trying to get in the results:

I would like the results sorted by totalDeals if at all possible.

CodePudding user response：

Use UNION ALL to get a resultset with all the rows for each salesperson, filter for the month that 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;

See the demo.