• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Sum function

 
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
 
Bartender
Posts: 1111
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

 
Ranch Hand
Posts: 859
IBM DB2 Chrome 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 L Gibbons
Bartender
Posts: 1111
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.
 
Ranch Hand
Posts: 163
Eclipse IDE Tomcat Server Java
  • 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.
    Bookmark Topic Watch Topic
  • New Topic