Re: left join with sum and group by

[Rick James]

Give this a try... First find all the employees (SELECT DISTINCT ...). Then use a cross join to get all dates and all employees. Finally do the LEFT JOIN as you tried.

SELECT a.date, a.weekday, a.workinghours,
       e.employee,
       sum(b.hours),
       sum(b.hours) - a.workinghours
    FROM a
    JOIN ( SELECT DISTINCT employee FROM b ) AS e
    LEFT JOIN b  ON a.date = b.date
    GROUP BY  a.date, e.employee

(Note the missing ON clause in the JOIN)