Compute by dept

[puredesi5]

Hi everyone.
I have a table that has following fields:
dept, emp, hours, etc.

I want (a) hours for each dept and (b)then total of all depts at the end
Dept Hours
A 10
B 20
C 25

55
------------------
I wrote following
select dept, sum(hours)
where ---------
group by dept

above works fine for (a) but when I add

compute sum(hours) at end, It doesn't work.
Any ideas?

 

[SQLBill]

Are the Departments in the DEPT column only once? Or several times?

If once then this will work:

SELECT
dept
, hours
, SUM(hours)
FROM
tablename
GROUP BY
dept
ORDER BY
dept asc

If there departments are entered more than once in DEPT, then you will need to use DISTINCT. And I think ROLLUP will do what you want.

SELECT
distinct dept
, sum(hours)
FROM
tablename
GROUP BY
dept WITH ROLLUP

-SQLBill

 

[puredesi5]

Thanks SQLBILL:

With ROLLUP it worked. (I will read more on ROLLUP_
I get
Dept Hours
A 10
B 20
C 25
NULL 55

I used order by dept DESC.

Is there a way to substitute 'TOTAL' for 'NULL'??

 

[JamesLean]

SELECT CASE WHEN GROUPING(dept) = 1 THEN 'TOTAL' ELSE dept END AS dept, SUM(hours) AS hours
FROM table
GROUP BY dept WITH ROLLUP

--James

 

[puredesi5]

James: You are genius! Thanks a lot! It worked exactly as I wanted it.

Thanks both of you James & SQLBILL.

 

[SQLBill]

I tested this and it worked:

SELECT
CASE WHEN GROUPING(dept) = 1)
THEN 'Total'
ELSE dept
END as Dept
, SUM(hours)
FROM
tablename
GROUP BY
dept WITH ROLLUP
ORDER BY
hours DESC

-SQLBill

 

[SQLBill]

OOOPPPS, I was too late. I guess I was testing while James was inputting.

BTW- refer to GROUPING, CASE, and ROLLUP in the BOL.

-SQLBill