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

Your query is invalid because the `Id` and `Amount` are not part of your aggregation. — Tim Biegeleisen, Sep 19 '17 at 11:21

score 1 · Answer 1 · answered Sep 19 '17 at 11:21

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;

answered Sep 19 '17 at 11:21

Gordon Linoff

1,242,037
58
646
786

Thanks @Gordon Linoff but i tried your query and it does not work as my result on the table above – Yahia Baiba Sep 19 '17 at 11:54

Bhushan · Answer 2 · 2017-09-20T07:04:42.717

0

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

edited Sep 20 '17 at 07:04

answered Sep 19 '17 at 11:33

Bhushan

595
3
9

Can I know version? – Bhushan Sep 19 '17 at 11:40
So, you have to enable the standard SQL behavior. Let me know you are getting an error or it's not giving your expected output. – Bhushan Sep 19 '17 at 11:49
I used as you told. I didn't use id in select clause. – Bhushan Sep 19 '17 at 11:51
Your format made it hard for me to see it. I edited your answer – sagi Sep 19 '17 at 11:54
thanks. I created a temp table to show 'Total'. I think it will give expected output. – Bhushan Sep 19 '17 at 11:56
@sagi But i really need Id in the select clause – Yahia Baiba Sep 19 '17 at 12:15
Then you need to use group_concat on id field. If you will not group_concat then it will return id of first one. – Bhushan Sep 19 '17 at 12:17
@Jack even with your query the result is not same with what i described above, please take a look and at my desired result table ..Thank you – Yahia Baiba Sep 19 '17 at 13:02
@Yahia, can you share a result of above query. – Bhushan Sep 20 '17 at 07:06

score 0 · Answer 3 · answered Dec 16 '19 at 09:45

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',''));

Mysql Rollup with multiple group

3 Answers3