0

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?

2
  • 1
    Why doesn't the ROLLUP work for you? Feb 15, 2015 at 17:01
  • ypercube ----- I want this value an extra column .
    – Shihab
    Feb 16, 2015 at 4:49

1 Answer 1

0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.