MySQL:按查询分组计数

Question

我有一个这样的表:

-----------------------------------|  Name   | Date      | Campaign  |-----------------------------------| John    | July      | Email     || Carl    | August    | Phone     || John    | July      | Phone     || Robert  | August    | Phone     || Carl    | September | Email     || John    | August    | Phone     || Carl    | August    | Email     || John    | July      | Email     || Robert  | September | Phone     || Carl    | August    | Email     |----------------------------------- 

我计算了按日期和事件分组的数据，但我想要一个额外的列来显示包含该事件的总行数

我使用的查询:

      SELECT campaign,name,      SUM(IF(date = 'July',1,0)) AS July,      SUM(IF(date = 'August',1,0)) AS August,      SUM(IF(date = 'September',1,0)) AS September      FROM table      GROUP BY name,campaign

但我想修改它以获得以下输出:

----------------------------------------------------------------------------------------| Campaign | Name | July | August | September | SUM July |  SUM August | SUM September |----------------------------------------------------------------------------------------  Email      John    2       0          0           2           2              1  Phone      John    1       1          0           1           3              1........

我想要包含按月区分的电话或电子邮件的所有值的总和的列

地址:http://sqlfiddle.com/#!9/8b797a/8

Answer 1

尝试以下:http://sqlfiddle.com/#!9/8b797a/24 :

select a.campaign,a.name,July, August,September,Julysum,Augustsum,Septembersum from 
(SELECT campaign,name,
  SUM(IF(date = 'July',1,0)) AS July,
  SUM(IF(date = 'August',1,0)) AS August,
  SUM(IF(date = 'September',1,0)) AS September
  FROM `table`
  GROUP BY campaign,name)a
  left join
  (select campaign,
  SUM(IF(date = 'July',1,0)) AS Julysum,
  SUM(IF(date = 'August',1,0)) AS Augustsum,
  SUM(IF(date = 'September',1,0)) AS Septembersum
  FROM `table`
  GROUP BY campaign)b on a.campaign=b.campaign
  where name='John'

输出:

campaign    name    July    August  September   campaign    Julysum Augustsum   Septembersum
Email      John     2      0       0           Email         2     2    1
Phone      John     1      1       0           Phone         1     3    1