MYSQL sum/Count fails inside inner join group by

I have a table that contains sales records:

Sale ID EmployeeId(FK) Employee 2
1 101 Null
2 102 Null
3 300 Bob

I have another table that contains employee records:

EmployeeId EmployeeName
101 Amanda
102 Bob
300 cicilia

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, 
       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. enter image description here Im running a mysql 8.0 database.

Ellen Hollington

Related posts