aspose file tools*
The moose likes JDBC and the fly likes Sum function Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sum function " Watch "Sum function " New topic
Author

Sum function

vinoth sathiya
Ranch Hand

Joined: Sep 20, 2011
Posts: 36
Dear Friends,

I want to use sum() function to sum the amount

please find the below details


Table Name : details

column name : id , name , reg no , amount

Datas :

id name regno amount
--------------------------

1 A 001 100

2 A 001 140

3 A 001 110

1 B 002 160

2 B 002 200


select id , regno , sum(amount) from details group by id,regno

If i execute above query means it not summing the amount . it is showing normal select query.

I want to sum all regno amount

my result would be

id regno amount
--------------------------

1 001 350

2 001 350

3 001 350

1 002 360

2 002 360

Is this any way to do like above result?


Thanks in advance
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

I am a bit confused by your post, as there is no repeating data in the example you supplied of the data, and it doesn't match the required output.


I just realised you don't want the id in your group by, as id and reg no is the unique key for each row.
vinoth sathiya
Ranch Hand

Joined: Sep 20, 2011
Posts: 36
Hi,

id and reg no is unique.

i want to sum all amount based on regno.

for e.g

if i execute " select sum(amount)amt,regno from details group by regno "

means i got below result

amt regno
------------
350 001

360 002


but i need id with above result

like below query

select id , sum(amount)amt , regno from details group by id,regno

i want below output

id regno amount
-------------------
1 001 350

2 001 350

3 001 350

1 002 360

2 002 360


I want to sum of amount based on regno. with id

William P O'Sullivan
Ranch Hand

Joined: Mar 28, 2012
Posts: 859

Are you trying to update the first table, the one with the amount column, or simply generating output?

WP
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Sorry I have no idea what you want to acheive, you have provided us with 5 rows, each row can by uniquely identified by the id and reg no.
So can you show me long hand which rows you want to be added together.

so for example id:1, regno:001 + id:2, reg no:002.
Bill Clar
Ranch Hand

Joined: Sep 21, 2006
Posts: 153

vinoth,

Sounds like you need to embed an extra sql query:



This approach works in Informix. I can't guarantee it will work in other databases.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Sum function