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'）; 这给了我以下结果： 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 1I'm having some trouble trying to achieve the following format: fname MONTH( eventDate ) Weekday COUNT WEEKEND COUNT Kevin 7 3 1 Missy 7 3 1Can 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 WeekendCountFROM eventcal AS eLEFT JOIN users AS u ON e.primary = u.usernameGROUP 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！