Sum by month

robojam · #1 (**permalink**) 11-10-04, 11:28

Hi

I have this PL/SQL:

Code:

SELECT 
to_char(FUND_DT, 'MON-YY') As Mth, 
SUM(DECODE(MARKET, 'Equity', DECODE(OBJECTIVE, 'Aggressive Growth', NET_SALES_AND_REDEMP))) AS S2,
SUM(DECODE(MARKET, 'Equity', DECODE(OBJECTIVE, 'Growth', NET_SALES_AND_REDEMP))) AS S3, 
SUM(DECODE(MARKET, 'Equity', DECODE(OBJECTIVE, 'Sector', NET_SALES_AND_REDEMP))) AS S4, 
SUM(DECODE(MARKET, 'Equity', DECODE(OBJECTIVE, 'Growth & Income', NET_SALES_AND_REDEMP))) AS S5, 
SUM(DECODE(MARKET, 'Equity', DECODE(OBJECTIVE, 'International', NET_SALES_AND_REDEMP))) AS S6, 
SUM(DECODE(MARKET, 'Equity', DECODE(OBJECTIVE, 'Hybrid', NET_SALES_AND_REDEMP))) AS S8, 
SUM(DECODE(MARKET, 'Money Market', DECODE(OBJECTIVE, 'MMF', NET_SALES_AND_REDEMP))) AS S10, 
SUM(DECODE(MARKET, 'Municipal Bond', DECODE(OBJECTIVE, 'Municipal Bonds', NET_SALES_AND_REDEMP))) AS S12, 
SUM(DECODE(MARKET, 'Taxable Bond', DECODE(OBJECTIVE, 'Corporate Bond', NET_SALES_AND_REDEMP))) AS S14, 
SUM(DECODE(MARKET, 'Taxable Bond', DECODE(OBJECTIVE, 'Government', NET_SALES_AND_REDEMP))) AS S15, 
SUM(DECODE(MARKET, 'Taxable Bond', DECODE(OBJECTIVE, 'High Yield', NET_SALES_AND_REDEMP))) AS S16, 
SUM(DECODE(MARKET, 'Taxable Bond', DECODE(OBJECTIVE, 'Strategic Income', NET_SALES_AND_REDEMP))) AS S17 
FROM PII.AMG_MUTUAL_FUNDS_DLY, PII.AMG_RULES_TBL 
WHERE PII.AMG_MUTUAL_FUNDS_DLY.FUND_SEQ = PII.AMG_RULES_TBL.FUND_SEQ AND 
FUND_DT > '01-Jan-2001'
GROUP BY FUND_DT

which will return a record for each date, but what I need to do is to return a record for each month with the sum of each month's data.

Can anyone think of a way that I can do this?

Thank you

Pat Phelan · #2 (**permalink**) 11-10-04, 12:03

GROUP BY to_char(FUND_DT, 'MON-YY')

-PatP

robojam · #3 (**permalink**) 11-10-04, 12:10

I was kicking myself after posting as I realized what you have posted is what I needed to do!

One other thing though - this orders the results using alphanumeric values of dates, is there a way to order them chronologically?

Thanks

LKBrwn_DBA · #4 (**permalink**) 11-10-04, 12:46

Try:

ORDER BY TO_CHAR(fund_dt,'YYYYMM')

robojam · #5 (**permalink**) 11-10-04, 13:06

The query doesn't work without the GROUP BY clause and the ORDER BY gives me a "Not a GROUP BY expression" error.

r937 · #6 (**permalink**) 11-10-04, 17:16

the two TO_CHARs are different, so just put both of them into the SELECT list and the GROUP BY, so that you can then ORDER BY one of them but then display the other

LKBrwn_DBA · #7 (**permalink**) 11-11-04, 19:09

What r937 means is to use both like this:

Code:

SELECT ...
  FROM ...
 GROUP BY to_char(FUND_DT, 'MON-YY')
 ORDER BY TO_CHAR(fund_dt,'YYYYMM')

r937 · #8 (**permalink**) 11-11-04, 19:21

no, i'm sorry, that is not what i meant!

what i mean is this --

Code:

select to_char(FUND_DT, 'MON-YY') as DisplayMonth
     , to_char(FUND_DT, 'YYYYMM') as OrderByMonth
     , ...
group 
    by to_char(FUND_DT, 'MON-YY') 
     , to_char(FUND_DT, 'YYYYMM') 
order
    by to_char(FUND_DT, 'YYYYMM')

LKBrwn_DBA · #9 (**permalink**) 11-12-04, 07:49

You actually do not need to include the 'ORDER BY' columns in the SELECT or GROUP BY statements!
The code I posted works.

r937 · #10 (**permalink**) 11-12-04, 07:52

hey, LKBrwn_DBA, do me a favour, stop shouting

your code might work on your database but it isn't standard sql

this forum is for standard sql (in case you didn't notice)

you can not order by a column that you don't select

LKBrwn_DBA · #11 (**permalink**) 11-12-04, 08:10

1) I am not shouting.
2) My code IS ansi SQL.

r937 · #12 (**permalink**) 11-12-04, 08:19

you are too shouting

look up any resource on the internet for "etiquette"

please reduce the size of your font

it's ostentatious, gaudy, disrespectul, and rude

perhaps you only meant it to be unique and distinctive, all i'm telling you is how it comes across, dude

as for your so-called ansi sql, please show a demonstrable script where your code works

so far, i flat out don't believe you

here, i have a script which you may customise:

Code:

create table LKBrwn_DBA  
( id tinyint not null primary key 
, foo varchar(9)
, bar varchar(9)
)

insert into LKBrwn_DBA (foo,bar) values ('one','cat')
insert into LKBrwn_DBA (foo,bar) values ('one','dog')
insert into LKBrwn_DBA (foo,bar) values ('one','rat')
insert into LKBrwn_DBA (foo,bar) values ('two','horse')
insert into LKBrwn_DBA (foo,bar) values ('two','tiger')

select foo, count(*)
from LKBrwn_DBA 
group by foo
order by bar

i would like to see you order by a column in a group by query that isn't in the select list

don't forget to mention which database it supposedly works in