I have a table that contains sales records:
|Sale ID||EmployeeId(FK)||Employee 2||…|
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.