Mysql Rollup with multiple group

Question

Mytable with Id as autoincrement Customer, Date and Amount I want to make a rollup that will sum the amount of every customer every day I mean that the rollup is grouped by Date and Customer This is what should my result look like :

+---------+--------------+--------------+------------+
| Id      | Date         | Customer     |     Amount |  
+---------+--------------+--------------+------------+
| 1       | 2017-09-19   |          B   |        10  | 
| 4       | 2017-09-19   |          B   |        15  | 
| 8       | 2017-09-19   |          B   |        02  | 
| 6       | 2017-09-19   |          B   |        18  |
| 5       | 2017-09-19   |          B   |        05  | 
| Total   | NULL         |          B   |        50  | 

| 9       | 2017-09-19   |         C    |        11  | 
|14       | 2017-09-19   |         C    |        10  |
| 12      | 2017-09-19   |         C    |        09  | 
| Total   | NULL         |         C    |        30  | 

| 11       | 2017-09-18  |         B    |         20 | 
| 15       | 2017-09-18  |         B    |         40 |
| Total    | NULL        |         B    |         60 | 

| 10       | 2017-09-18  |         A    |         1  | 
| 13       | 2017-09-18  |         A    |         1  | 
| 16       | 2017-09-18  |         A    |         1  | 
| 7        | 2017-09-18  |         A    |         1  |
| 3        | 2017-09-18  |         A    |         1  | 
| Total    | NULL        |         A    |         5  | 

| 2        | 2017-09-18   |         C   |        90  | 
| Total    | NULL         |         C   |        90  | 

I tried :

Select
Mytable.Id,
Mytable. Date,
Mytable.Customer,
Mytable.Amount
From Mytable
GROUP BY Mytable. Date, Mytable.Customer WITH ROLLUP

But this does not work as expected .please help

Answer 1

You need an aggregation. Does this do what you want?

select t.Date, t.Customer,
       sum(t.Amount) as Amount
from Mytable t
group by t.Date, t.Customer with rollup;

If you actually really do want the id, then:

select t.id, t.Date, t.Customer,
       sum(t.Amount) as Amount
from Mytable t
group by t.id, t.Date, t.Customer with rollup;

Answer 2

You need to add id column on group by

For Version <= 5.7

Query:

select 
    coalesce(id, 'Total') as id,
    date,
    customer,
    sum(amount) as amount
from
    mytable
group by date, customer, id with ROLLUP
having customer is not null;

Result:

id    | date       | customer | amount
:---- | :--------- | :------- | -----:
3     | 2017-09-18 | A        |      1
7     | 2017-09-18 | A        |      1
10    | 2017-09-18 | A        |      1
13    | 2017-09-18 | A        |      1
16    | 2017-09-18 | A        |      1
Total | 2017-09-18 | A        |      5
11    | 2017-09-18 | B        |     20
15    | 2017-09-18 | B        |     40
Total | 2017-09-18 | B        |     60
2     | 2017-09-18 | C        |     90
Total | 2017-09-18 | C        |     90
1     | 2017-09-19 | B        |     10
4     | 2017-09-19 | B        |     15
5     | 2017-09-19 | B        |      5
6     | 2017-09-19 | B        |     18
8     | 2017-09-19 | B        |      2
Total | 2017-09-19 | B        |     50
9     | 2017-09-19 | C        |     11
12    | 2017-09-19 | C        |      9
14    | 2017-09-19 | C        |     10
Total | 2017-09-19 | C        |     30

---

For Version > 5.7

Query:

select 
    coalesce(id, 'Total') as id,
    date,
    customer,
    sum(amount) as amount
from
    mytable
group by date, customer, id with ROLLUP
having grouping(customer) = 0;

Result:

id    | date       | customer | amount
:---- | :--------- | :------- | -----:
3     | 2017-09-18 | A        |      1
7     | 2017-09-18 | A        |      1
10    | 2017-09-18 | A        |      1
13    | 2017-09-18 | A        |      1
16    | 2017-09-18 | A        |      1
Total | 2017-09-18 | A        |      5
11    | 2017-09-18 | B        |     20
15    | 2017-09-18 | B        |     40
Total | 2017-09-18 | B        |     60
2     | 2017-09-18 | C        |     90
Total | 2017-09-18 | C        |     90
1     | 2017-09-19 | B        |     10
4     | 2017-09-19 | B        |     15
5     | 2017-09-19 | B        |      5
6     | 2017-09-19 | B        |     18
8     | 2017-09-19 | B        |      2
Total | 2017-09-19 | B        |     50
9     | 2017-09-19 | C        |     11
12    | 2017-09-19 | C        |      9
14    | 2017-09-19 | C        |     10
Total | 2017-09-19 | C        |     30

---

Fiddle:
db<>fiddle here

Note: When using group by without ERROR 1055 you need to set as

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Answer 3

I think below query will solve your problem.

SELECT IF(temp.date IS NULL, 'Total', temp.Id), temp.Date, temp.Customer, temp.Amount
FROM
     (Select group_concat(Mytable.Id),
             Mytable.Date,
             Mytable.Customer,
             SUM(Mytable.Amount)
      From Mytable
      GROUP BY Mytable. Date, Mytable.Customer WITH ROLLUP)) AS temp