Day: January 2, 2009

Having a stupid moment and maybe you can help. I’m trying to count how many times an employee is scheduled using MySQL. I would like to have a column that for George says "3" and for Lucy says "2" but if I group on EmployeeID and use count(e.EmployeeID) I end up with 2 rows instead of 5. How do I retain the 5 rows of data and still get a count of 3 for George and 2 for Lucy? My output will show that George has schedule Sat-Mon, Tues-Fri, and Wed-Sun and will say "George is scheduled 3 times."

CompanyID AffiliateID EmployeeID EmployeeName Schedule
1   23   11   George   Sat-Mon
1   23   11   George   Tues-Fri
1   23   11   George   Wed-Sun
1   23   15   Lucy   Sun-Mon
1   23   15   Lucy   Thur-Fri

SELECT c.CompanyID,
     a.AffiliateID,
     e.EmployeeID,
     e.EmployeeName,
     s.Schedule
FROM companies c
LEFT OUTER JOIN subscription s
     ON c.CompanyID = s.CopmanyID
LEFT OUTER JOIN affiliates a
     ON a.AffiliateID = s.AffiliateID
LEFT OUTER JOIN employees e
     ON ae.AffiliateID = a.AffiliateID
LEFT OUTER JOIN schedules s
     ON s.EmployeeID = e.EmployeeID
          AND s.CompanyID = c.CompanyID
          AND s.AffiliateID = a.AffiliateID
WHERE (s.StartDate <= FROM_UNIXTIME(1227243599)
     AND s.EndDate >= FROM_UNIXTIME(1227157200))
     AND c.CompanyID = 1
ORDER BY c.CompanyName, s.AffiliateID ASC

I know this is not a complex SQL problem. I’ve done this in the past but today I am being dense and am stumped.