DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL Query That Uses both DECODE & SUM

  1. #1
    Join Date
    Mar 2004
    Posts
    55

    SQL Query That Uses both DECODE & SUM

    I am looking for assistance with a SQL query that will

    i) Do a Sum on a numeric field
    ii) Group by say category down the side and transpose by month across the top.

    I have been looking at DECODE and I do not have problems using the count function with a date mask however I am stuck with doing a SUM.

    My table looks as follows
    CAT DATE QTY
    a 01/01/2007 40
    a 21/01/2007 30
    a 01/02/2007 50
    b 01/01/2007 80
    b 15/01/2007 60
    b 08/02/2007 40


    I would like it to come out as

    JAN FEB
    a 70 50
    b 140 40


    I would like to avoid PL/SQL


    Thanks

  2. #2
    Join Date
    May 2000
    Location
    Richardson, Texas, USA
    Posts
    39
    I don't think you can do it using just a single query.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    pivot query to the rescue...

    Code:
    select  cat,
            max(decode(rn,1,summ)) "JAN",
            max(decode(rn,2,summ)) "FEB"
    from
    (        
    select  cat, 
            "MONTH", 
            sum(qty) "SUMM" , row_number()
            over (partition by cat order by "MONTH") RN  
    from  ( select  cat, 
                    decode(TO_CHAR(mydate,'mm'),'01','01-JAN','02','02-FEB','ETC') "MONTH",
                    qty                
            from    testcase
          )
    group by cat,"MONTH"
    )
    group by cat;
    CAT JAN FEB
    a 70 50
    b 140 40
    Last edited by PAVB; 06-29-2007 at 08:39 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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