MySQL: Count total in Group by Query

1
votes

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

2
mysql
Hint: you'll be needing sub-queries to resolve your issue. - Alexandre Elshobokshy
it's a choice I wanted to apply, but I can not find the solution. - horez
Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). No subqueries required. - Strawberry
Exporting data to SQL I can not use PHP scripts - horez

2 Answers

2
votes

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

I realise this isn't exactly what you asked for, but it's a simple way to get totals using GROUP BY ... WITH ROLLUP:

SELECT Campaign, Name,
  SUM(CASE WHEN Date='July' THEN 1 ELSE 0 END) AS July,
  SUM(CASE WHEN Date='August' THEN 1 ELSE 0 END) AS August,
  SUM(CASE WHEN Date='September' THEN 1 ELSE 0 END) AS September
FROM campaigns
GROUP BY Campaign, Name WITH ROLLUP

Output:

Campaign    Name    July    August  September
Email       Carl    0       2       1
Email       John    2       0       0
Email       (null)  2       2       1
Phone       Carl    0       1       0
Phone       John    1       1       0
Phone       Robert  0       1       1
Phone       (null)  1       3       1
(null)      (null)  3       5       2

The totals of each month for each type of campaign are shown in the row with the campaign name and a NULL value for Name. The grand total for each month (of both types of campaign) is shown in the bottom row with NULL values for Campaign and Name.

SQLFiddle Demo