MySQL Weekday / Weekend count - Part II [英] MySQL Weekday/Weekend count - Part II

问题描述

$ b

  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'）; 
  

这给了我以下结果：

< pre $ fname MONTH（eventDate）日计数（*）
Kevin 7 weekday 3
Kevin 7周末1
Missy 7周一3
Missy 7周末1


我在尝试实现以下格式时遇到了一些麻烦：

  fname MONTH（eventDate）星期几COUNT WEEKEND COUNT 
 Kevin 7 3 1 
小姐7 3 1 
  

任何人都可以提供一些帮助吗？我将不胜感激...

你可以在'user'和'eventcal'中看到我的模式：

选择
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用户AS u ON e.primary = u.username
GROUP BY fname，MONTH（eventDate）;

您希望在SELECT和GROUP BY中执行聚合（在这种情况下为SUM）希望他们总计（由fname，由MONTH）。

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: MySQL/PHP Algorithm for Weekday/Weekend count (per month)

解决方案

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).

这篇关于MySQL Weekday / Weekend count - Part II的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！