Group by date with a SUM - Can't think!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Group by date with a SUM - Can't think!!

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Group by date with a SUM - Can't think!!

    Hi,

    Hopefully, someone will have a quick and easy answer for me...

    I have a (theoretical) table with 2 columns, POSTING_DATE and AMOUNT

    POSTING_DATE can be any day of the year.

    I need to output something that looks like this.....

    Month Amount Sum

    Jan-04 123,456 (whatever the sum is)
    Feb-04 345,678
    Mar-04
    ...
    ...
    Dec-04 999,999


    I'm looking at 'Select posting_date,sum(amount)
    from table
    group by posting_date;


    But I'm having trouble grouping into months, and struggling with the date mask.

    Please help, can't think today. Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    TRUNC(posting_date, 'MON') in both select & group by should do it.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Thanks Dapi, (new icon looks slightly menacing, gulp!)

    Your code outputs as follows....

    Code:
    select TRUNC(posting_date,'MON'), sum(amount)
    from w_card_transaction ct
    where posting_date > to_date('31-12-2003','DD-MM-YYYY')
    and posting_date < to_date('01-01-2005','DD-MM-YYYY')
    group by TRUNC(posting_date,'MON')
    order by TRUNC(posting_date,'MON');
    
    TRUNC(POSTING_DATE,' SUM(AMOUNT)
    -------------------- -----------
    01-Jan-2004 00:00:00    73421822
    01-Feb-2004 00:00:00    65580379
    01-Mar-2004 00:00:00    73544607
    01-Apr-2004 00:00:00    68714657
    01-May-2004 00:00:00    69430156
    01-Jun-2004 00:00:00    71043087
    01-Jul-2004 00:00:00    73624546
    01-Aug-2004 00:00:00    80102705
    01-Sep-2004 00:00:00    84481250
    01-Oct-2004 00:00:00    93033185
    01-Nov-2004 00:00:00   110017083
    01-Dec-2004 00:00:00   100254770
    I need to remove the DAY format, as this will only confuse the client.

    So far I've got...

    Code:
     select to_char(posting_date,'Mon-YYYY'), sum(amount)
    from w_card_transaction
    where posting_date > to_date('31-12-2003','DD-MM-YYYY')
    and posting_date < to_date('01-01-2005','DD-MM-YYYY')
    group by to_char(posting_date,'Mon-YYYY')
    order by to_char(posting_date,'Mon-YYYY');
    
    TO_CHAR( SUM(AMOUNT)
    -------- -----------
    Apr-2004    68714657
    Aug-2004    80102705
    Dec-2004   100254770
    Feb-2004    65580379
    Jan-2004    73421822
    Jul-2004    73624546
    Jun-2004    71043087
    Mar-2004    73544607
    May-2004    69430156
    Nov-2004   110017083
    Oct-2004    93033185
    Sep-2004    84481250
    But the order by clause on a 'to_char' is screwing me up now....

    Unless you have a more elegant solution?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    How about this?

    Code:
    SELECT TO_CHAR( posting_date, 'YYYYMM' ) posting_date, 
               SUM( amount ) sum_amt
      FROM w_card_transaction
     WHERE posting_date > TO_DATE( '31-12-2003', 'DD-MM-YYYY' ) AND
           posting_date < TO_DATE( '01-01-2005', 'DD-MM-YYYY' )
     GROUP BY TO_CHAR( posting_date, 'YYYYMM' )
     ORDER BY TO_CHAR( posting_date, 'YYYYMM' );
    this space intentionally left blank

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Haven't tested it, but this might work ...
    Code:
    select to_char(posting_date,'Mon-YYYY'), sum(amount)
    from w_card_transaction
    where posting_date > to_date('31-12-2003','DD-MM-YYYY')
    and posting_date < to_date('01-01-2005','DD-MM-YYYY')
    group by to_char(posting_date,'Mon-YYYY'),Trunc(posting_date,'MON')
    order by Trunc(posting_date,'MON');
    Alternatively ...
    Code:
    Select to_char(mth,'Mon-YYYY'),amt
    From
    (
    select Trunc(posting_date,'MON') mth, sum(amount) amt
    from w_card_transaction
    where posting_date > to_date('31-12-2003','DD-MM-YYYY')
    and posting_date < to_date('01-01-2005','DD-MM-YYYY')
    group by Trunc(posting_date,'MON')
    order by 1
    )
    Ref DaPi's avatar -- have you ever read "The Picture of Dorian Gray"? 'Nuff said.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Thumbs up

    Thanks Guys for the help.

    Gandolf, your code kept throwing this error at me....

    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P019
    ORA-01481: invalid number format model

    Dave,

    Both your scripts worked a treat, but I prefer the look of the first one, no noticable difference in query performance between the two.

    Cheers!!

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Dapi, (new icon looks slightly menacing, gulp!)

    Ref DaPi's avatar -- have you ever read "The Picture of Dorian Gray"? 'Nuff said.
    It's from the same photo as the old one, just shrunk & cropped differently (6KB and 80x80 pixel limit). I think I posted the complete thing once . . . . in Obfuscation I should think.

    Of course I'm even older now.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

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



Click Here to Expand Forum to Full Width