mysql sum(column2 - column1) with rollup

Question

i need some help with my mysql query . I have one table that look like :

And then i want to select with this query :

select id, class, defaut, input, round(input - defaut) test from table1
group by class, id with rollup

I want the output like :

But my query given me like that :

please for your help, thanks

I think you could illustrate the problem with fewer rows and/or an sqlfiddle/rextest. — Strawberry, May 10 '17 at 10:07
You SELECT id, class, defaut, and input, but only group by two of those columns. While there is a functional dependancy in place, for the purposes of ROLLUP, this doesn't quite make sense. — Strawberry, May 10 '17 at 10:17

score 2 · Accepted Answer · answered May 10 '17 at 10:35

You need to sum the default, input and the calculated fields to get the expected output, otherwise rollup will simply return the value from the last record:

select id, class, sum(defaut), sum(input), sum(round(input - defaut)) test from table1
group by class, id with rollup

score 0 · Answer 2 · answered May 12 '17 at 02:27

0

Final Query :

select id, class, sum(defaut), sum(input), sum(input - defaut) test from
 table1 
group by class, id with rollup

Thank for your helps.

answered May 12 '17 at 02:27

Mas Harjo

73
9

mysql sum(column2 - column1) with rollup

2 Answers2