Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting totals of rows in SQL

 
Nitin Jawarkar
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
cheers
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic