MySQL Weekday/Weekend count - Part II

Question

Hey everyone,

I have posted about this before, which helped to give me the following SQL:

 SELECT fname, MONTH( eventDate ) , IF( WEEKDAY( eventDate ) <5, 'weekday', 'weekend' ) AS
 DAY , COUNT( * )
 FROM eventcal AS e
 LEFT JOIN users AS u ON e.primary = u.username
 GROUP BY fname, MONTH( eventDate ) , IF( WEEKDAY( eventDate ) <5, 'weekday', 'weekend' ) ;

And that gives me the following results:

 fname  MONTH( eventDate )  DAY  COUNT( * )
 Kevin  7                  weekday  3
 Kevin  7                  weekend  1
 Missy  7                  weekday  3
 Missy  7                  weekend  1

I'm having some trouble trying to achieve the following format:

 fname  MONTH( eventDate )  Weekday COUNT    WEEKEND COUNT
 Kevin   7                  3                  1
 Missy     7                   3                  1

Can anyone offer some help? I would greatly appreciate it...

You can see my schemas for 'user' and 'eventcal' at: http://stackoverflow.com/questions/1140596/mysql-php-algorithm-for-weekday-weekend-count-per-month

Answer 1

SELECT 
  fname, 
  MONTH(eventDate), 
  SUM(IF(WEEKDAY(eventDate) < 5,1,0)) AS WeekdayCount,
  SUM(IF(WEEKDAY(eventDate) >= 5,1,0)) AS WeekendCount
FROM eventcal AS e
LEFT JOIN users AS u ON e.primary = u.username
GROUP BY fname, MONTH(eventDate);

You want to do your aggregations (SUM in this case) in the SELECT, and GROUP BY how you want them totaled (by fname, by MONTH).