I have a table that contains sales records:

I have another table that contains employee records:

I'm trying to do a select where i get all sales and group them by employees for performance analysis. So far i managed to get right the employees and their sale counts and totals. The problem is the third column in my sales record is called employee2, it can be null as not every sale has another employee assisting. It is not indexed to the employee table unlike the second column.

So for example in my query below, the expected results should be Amanda has 1 salecount, 0 helpCount, meanwhile Boss has 1 salecount, 1 helpCount, and cicillia has 1 salecount, 0 helpcount. But im getting 1 salecount for all which is correct, but 0 helpcounts for bob. This is my query so far:

select employee.employee_id, 
       employee.employee_Name, 
       count(sale.sale_id) as saleCount, 
       sum(sale.grand_total) as totalSalesRevenue, 
       sum(CASE WHEN sale.employee2 = employee.employee_Name THEN 1 
                ELSE 0 END) as helperEmpCount
from employee 
inner join sale on employee.employee_id = sale.employee_id 
group by employee.employee_id;

The result set, where helpCounts should not be 0. Im running a mysql 8.0 database.

Edit: I have found a workaround, albeit a very unefficient one. If i change my count to a nested select it works, but this decreases performance by quite a bit considering i have a lot of employees.

New query:

select employee.employee_id, 
       employee.employee_Name, 
       count(sale.sale_id) as saleCount, 
       sum(sale.grand_total) as totalSalesRevenue, 
       (select count(sale.employee2) from sale where sale.employee2= employee_Name) as helperEmpCount
from employee 
inner join sale on employee.employee_id = sale.employee_id 
group by employee.employee_id;

Any idea how to make it more efficient?

CodePudding user response：

You can join the tables on either of the 2 conditions and use conditional aggregation:

SELECT e.employee_id, 
       e.employee_Name, 
       SUM(s.employee_id = e.employee_id) AS saleCount, 
       SUM(CASE WHEN s.employee_id = e.employee_id THEN s.grand_total ELSE 0 END) AS totalSalesRevenue, 
       SUM(s.employee2 = e.employee_Name) AS helperEmpCount
FROM employee e LEFT JOIN sale s
ON s.employee_id = e.employee_id OR s.employee2 = e.employee_Name
GROUP BY e.employee_id;