MySQL Sum of a Column – Handling Pre-Summed Values

group byMySQLsum

This is my query:

select d.department_id, d.department_name, sum(e.salary) AS total
from department d inner join employee e on d.department_id=e.department_id
group by d.department_id,d.department_name

I want the Grand total by sum of total column (the alias column). I get this by using this query:

select d.department_id, d.department_name, sum(e.salary) AS total
from department d inner join employee e on d.department_id=e.department_id
group by d.department_id,d.department_name WITH ROLLUP

But I want this by a another column, like using the sum function. Is it possible?

Best Answer

Case 1: Do you want the extra column to have only one entry, at the bottom?

Case 2: Do you want a column with a running total that increases row by row?

For Case 1, there are two approaches:

1a: Do it in your application code; this is probably cleaner.

1b: You want to do it entirely in SQL:

SELECT ... (what you have, plus an extra column with an empty string) ...
UNION ALL
SELECT (a bunch of blanks, then SUM(to get grand total) ...

For Case 2, you need an @variable that keeps the running total:

SELECT ... SUM(e.salary) AS ..., @run := @run + SUM(e.salary) AS ... ...

To get @run initialized, you could either have a separate SET statement or fold it into this query by using a 'cross join': JOIN ( SELECT @run := 0 ) AS z (with no ON clause).

Related Question