1. Registered User
Join Date
Sep 2003
Posts
10

I have a question regarding doing multiple queries at the sametime in DB2. I need to get the sum of a amount based on the year it occured in. and I need to do it for multiple years. so instead of doing the following simplified query 10 times for 10 different years:

select sum(amount)
from table1
where date between '1/1/2000' and '12/31/2000'

get the result and then do the next year

select sum(amount)
from table1
where date between '1/1/2001' and '12/31/2001'

Is there a way to lump all of the above into ONE query?

Any help would be much appreciated.

DB2 Newbie

2. Registered User
Join Date
Dec 2002
Posts
134

select year(date), sum(amount)
from table1
group by year(date)

P.S.
In future please post with a meaningful subject

regards,
dima

3. Registered User
Join Date
Sep 2003
Posts
10
Thanks so much!!

4. Registered User
Join Date
Sep 2003
Posts
10
How about if I want to do the same query by quarters for the multiple years? is there way to do this? Basically I am trying to ask if there is a way to group together a bunch of criterias in the same query. For example, I want to group the amount by credit rating ranges like

select sum(amount)
from table1
where rating between 1 and 10

get the result and then run

select sum(amount)
from table1
where rating between 11 and 20

until I hit 100

Is there a way to do the above in 1 query instead of 10 queries

5. Registered User
Join Date
Dec 2002
Posts
134
There is a quarter function in db2.
You can use any function in group by, in your case you can just divide rating by 10 (integer divided by integer will give an integer number, no need for round)

select rating/10, sum(amount)
from table1
where rating between 1 and 100
group by rating/10

I suggest you read some books on SQL, like DB2 SQL Cookbook

http://ourworld.compuserve.com/homep...l/HOMEPAGE.HTM

6. Registered User
Join Date
Sep 2003
Posts
10
Dude, thanks a lot

Originally posted by chuzhoi
There is a quarter function in db2.
You can use any function in group by, in your case you can just divide rating by 10 (integer divided by integer will give an integer number, no need for round)

select rating/10, sum(amount)
from table1
where rating between 1 and 100
group by rating/10

I suggest you read some books on SQL, like DB2 SQL Cookbook

http://ourworld.compuserve.com/homep...l/HOMEPAGE.HTM

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•