• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sum function

 
vinoth sathiya
Ranch Hand
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1110
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 36
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 859
Chrome IBM DB2 Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you trying to update the first table, the one with the amount column, or simply generating output?

WP
 
Wendy Gibbons
Bartender
Posts: 1110
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 163
Eclipse IDE Java Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic