Getting totals of rows in SQL

[Nitin Jawarkar]

Hello all,

Here is one simple query in which i got result what i want but how to use to display,

select emp_dept,sum(sal)"salary",sum(bonus)"bonus"
from employee
group by emp_dept
order by emp_dept
compute sum(sum(sal)),sum(sum(bonus))

it gives the total of all salary of departmentwise but it gives error when i used sum(sum(sal))"tot_sal" like this and i want to use the some name for total sal and bonus, is possible in sql server command.
Any helpping solution is appreciated.

Thanx

[Shailesh Chandra]

Nitin,

I havrn't worked with sql server but i remeber there is a function rollup
which will give you desired result

try this query

select emp_dept,sum(sal)"salary",sum(bonus)"bonus"
from employee
group by emp_dept
order by emp_dept WITH ROLLUP

This should give you an additional row having desired result.

thanks
[ February 04, 2005: Message edited by: Shailesh Chandra ]

[Nitin Jawarkar]

Thanx Shailesh

My problem is solved with your solution but in my query like compute clause, should have alies to display result ,i search on google it but it doesn't have, ok thanx again.

Thanx

[Shailesh Chandra]

cheers

[David Harkness]

I suspect the problem may be related to this tidbit from the SQL Server docs

[S]tatements that include compute do not generate normal rows.

Looking at the syntax for the computer clause, there isn't a way to add a label. Therefore, it produces a result set with varying number of columns. This probably confuses the JDBC driver.