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

[Horace]

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.

[DaPi]

TRUNC(posting_date, 'MON') in both select & group by should do it.

[Horace]

Thanks Dapi, (new icon looks slightly menacing, gulp!)

Your code outputs as follows....

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

 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?

[gandolf989]

How about this?

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' );

[slimdave]

Haven't tested it, but this might work ...

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

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.

[Horace]

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!!

[DaPi]

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.