MySQL: Count total in Group by Query

Question

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

it's a choice I wanted to apply, but I can not find the solution. — horez
– horez, Commented Sep 12, 2018 at 8:24
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
– Strawberry, Commented Sep 12, 2018 at 8:25

Fahmi · Accepted Answer · 2018-09-12 08:54:15Z

2

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

edited Sep 12, 2018 at 8:54

answered Sep 12, 2018 at 8:38

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

horez Over a year ago

Thank you. This answer is very close to the solution, but there is 2 times the campaign column and having to export a SQL that is imported into a system where I can not add a column, is a small problem.

Fahmi Over a year ago

I've update the column selection - you can check now

Nick · Accepted Answer · 2018-09-12 08:37:53Z

0

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

answered Sep 12, 2018 at 8:37

Nick

147k23 gold badges67 silver badges106 bronze badges

2 Comments

horez Over a year ago

thanks for the help, it's not a good solution for me, I need them as columns not as rows.

Nick Over a year ago

No worries, I wasn't sure how constrained you were so thought I should offer this up

Collectives™ on Stack Overflow

MySQL: Count total in Group by Query

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related