-
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.
-
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
-
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?
-
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' );
-
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.
-
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, (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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|