Results 1 to 14 of 14

Thread: Sum by month

  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Sum by month

    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
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    GROUP BY to_char(FUND_DT, 'MON-YY')

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    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
    Make something idiot proof and someone will make a better idiot...

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try:

    ORDER BY TO_CHAR(fund_dt,'YYYYMM')

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    The query doesn't work without the GROUP BY clause and the ORDER BY gives me a "Not a GROUP BY expression" error.
    Make something idiot proof and someone will make a better idiot...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    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')
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You actually do not need to include the 'ORDER BY' columns in the SELECT or GROUP BY statements!
    The code I posted works.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    1) I am not shouting.
    2) My code IS ansi SQL.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Your example is incorrect because the column in the ORDER BY (FUND_DT) is in the select!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are still shouting and i will have nothing further to do with you

    good luck in your database endeavours
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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