Results 1 to 6 of 6

Thread: Please help!!!

  1. #1
    Join Date
    Sep 2003
    Posts
    10

    Unanswered: Please help!!!

    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. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: Please help!!!

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

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

    regards,
    dima

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

  4. #4
    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

    Thanks in advance

  5. #5
    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. #6
    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
  •