aspose file tools*
The moose likes JDBC and the fly likes Getting totals of rows in SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting totals of rows in SQL" Watch "Getting totals of rows in SQL" New topic
Author

Getting totals of rows in SQL

Nitin Jawarkar
Ranch Hand

Joined: Dec 18, 2004
Posts: 79
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


Cheers<br />Nitin
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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 ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Nitin Jawarkar
Ranch Hand

Joined: Dec 18, 2004
Posts: 79
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

Joined: Aug 13, 2004
Posts: 1081

cheers
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Getting totals of rows in SQL