caccia caccia - 6 months ago 20
MySQL Question

MySQL Select rows having sum(criteria1) <> sum(criteria2)

I'm sure the answer is out there somewhere, but can't find it...

One table stores logged hours for employees, classified by type (type = Project or Task).
For each day, I can have hours logged on multiple tasks.

By doing a simple sum and grouping I can get the total hours per type and per day, but I'd like to go one step further:
display only the "error cases" where sum(hours) per Project is different than sum(hours) for all Tasks for a given date.

mysql> select sum(logged), type, date from `hoursLog` group by idEmployee, date, type;
+-------------+----------+----------+
| sum(logged) | type | date |
+-------------+----------+----------+
| 0.8 | Project | 20160525 |
| 1.0 | Task | 20160525 |
| 0.3 | Project | 20160526 |
| 0.3 | Task | 20160526 |
| 0.3 | Project | 20160527 |
| 0.5 | Task | 20160527 |
+-------------+----------+----------+


From the above table, I want only the dates 20160525 and 20160527, for which the sum is different.

Appreciate any help!

Answer
SELECT
 SUM(IF(`type`='Task',logged,0)) task_hours,
 SUM(IF(`type`='Project',logged,0)) project_hours,
 date 
FROM `hoursLog` 
GROUP by idEmployee, date
HAVING task_hours <> project_hours

But I am not sure if you really need GROUP BY idEmployee. I guess this works now just because you have few records in test DB with only 1 employee involved. Do you need time summarized per person? or just per date?