By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
set sql_mode='';
CREATE TABLE `mytable` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`customer` varchar(10) DEFAULT NULL,
`amount` int(11) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-19', 'B', '10');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-18', 'C', '90');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-18', 'A', '1');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-19', 'B', '15');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-19', 'B', '5');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-19', 'B', '18');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-18', 'A', '1');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-19', 'B', '2');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-19', 'C', '11');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-18', 'A', '1');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-18', 'B', '20');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-19', 'C', '9');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-18', 'A', '1');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-19', 'C', '10');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-18', 'B', '40');
INSERT INTO `mytable` (`date`, `customer`, `amount`) VALUES ('2017-09-18', 'A', '1');
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;
| 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 |
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;
| 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 |