MySQL:按查询计算分组总数 [英] MySQL: Count total in Group by Query

问题描述

我有一张这样的桌子:

<前>---------------------|姓名 |日期 |活动 |---------------------|约翰 |七月 |电子邮件 ||卡尔 |八月 |电话 ||约翰 |七月 |电话 ||罗伯特 |八月 |电话 ||卡尔 |九月 |电子邮件 ||约翰 |八月 |电话 ||卡尔 |八月 |电子邮件 ||约翰 |七月 |电子邮件 ||罗伯特 |九月 |电话 ||卡尔 |八月 |电子邮件 |---------------------

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

我使用的查询:

<前>选择广告系列，名称，SUM(IF(date = 'July',1,0)) AS 七月，SUM(IF(date = 'August',1,0)) AS 八月，SUM(IF(date = 'September',1,0)) AS 九月发件人表按名称分组，广告系列

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

<前>---------------------------------------------------------------------------------|活动 |姓名 |七月 |八月 |九月 |SUM 七月 |SUM 八月 |SUM 九月 |---------------------------------------------------------------------------------电子邮件约翰 2 0 0 2 2 1电话约翰 1 1 0 1 3 1........

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

添加:http://sqlfiddle.com/#!9/8b797a/8

解决方案

试试下面的:http://sqlfiddle.com/#!9/8b797a/24:

select a.campaign,a.name,July, August,September,Julysum,Augustsum,Septembersum from(选择广告系列、名称、SUM(IF(date = 'July',1,0)) AS 七月，SUM(IF(date = 'August',1,0)) AS 八月，SUM(IF(date = 'September',1,0)) AS 九月从`表`GROUP BY 广告系列,名称)a左连接(选择广告系列，SUM(IF(date = 'July',1,0)) AS Julysum,SUM(IF(date = 'August',1,0)) AS Augustsum,SUM(IF(date = 'September',1,0)) AS Septembersum从`表`GROUP BY 广告系列)b 上 a.campaign=b.campaign哪里名字='约翰'

输出:

活动名称 July August 九月活动 Julysum Augustsum Septembersum电子邮件约翰 2 0 0 电子邮件 2 2 1电话约翰 1 1 0 电话 1 3 1

I have a table like this:

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

I count the data grouped by date and campaign, but I would like an additional column to bring me the total number of lines that have that campaign

the query I use:

      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

but I would like to modify it to get the following output:

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

I would like the columns with the total of all the values with phone or email distinguished by month

ADD: http://sqlfiddle.com/#!9/8b797a/8

解决方案

Try below: 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'

Output:

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

这篇关于MySQL:按查询计算分组总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！