1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

AVG by Month & Trouble With Correct ORDER BY clause 'YYYY/MM'

Discussion in 'SQL PL/SQL' started by CTOROCK, Dec 10, 2010.

  1. CTOROCK

    CTOROCK Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi, I have a table that I'd like to sum up the refills for each month, and eventually get an average. I have this query, it does order by year but the months are all over the place! Any suggestions, I've tried many other statements with the ORDER BY. Also, is there an easy way to get an average per month? Thanks in advance for any suggestions.


    SELECT TO_CHAR(MY_DATE, 'YYYY/MM') "Date",
    SUM(REFILL_QTY) "Sum of Refills by Month"
    FROM MY_TABLE
    GROUP BY TO_CHAR(MY_DATE, 'YYYY/MM')
    ORDER BY TO_CHAR(MY_DATE, 'YYYY/MM')
    /
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't see the problem:

    Code (SQL):
    SQL> CREATE TABLE my_table(
      2  my_date DATE,
      3  my_item varchar2(40),
      4  refill_qty NUMBER,
      5  cust_id NUMBER);
    TABLE created.
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000 loop
      3                  INSERT INTO my_table
      4                  VALUES(sysdate - MOD(i,433), 'Stuff', MOD(i,37)*12, i);
      5          END loop;
      6
      7          commit;
      8  END;
      9  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> SELECT TO_CHAR(MY_DATE, 'YYYY/MM') "Date",
      2  SUM(REFILL_QTY) "Sum of Refills by Month"
      3  FROM MY_TABLE
      4  GROUP BY TO_CHAR(MY_DATE, 'YYYY/MM')
      5  ORDER BY TO_CHAR(MY_DATE, 'YYYY/MM')
      6  /
    DATE    SUM OF Refills BY MONTH
    ------- -----------------------
    2009/10                   10692
    2009/11                   12744
    2009/12                   13032
    2010/01                   14832
    2010/02                   12636
    2010/03                   12672
    2010/04                   11916
    2010/05                   13464
    2010/06                   13728
    2010/07                   14700
    2010/08                   20832
    DATE    SUM OF Refills BY MONTH
    ------- -----------------------
    2010/09                   19188
    2010/10                   19800
    2010/11                   19020
    2010/12                    6540
    15 ROWS selected.
    SQL>
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    SELECT DISTINCT
           to_char(my_date,'yyyymm'),
           SUM(refill_qty) OVER (partition BY to_char(my_date,'yyyymm')) "sum of refills by month",
           avg(refill_qty) OVER (partition BY to_char(my_date,'yyyymm')) "avg of refills by month",
           SUM(refill_qty) OVER (partition BY to_char(my_date,'yyyy')) "sum of refills by year",
           avg(refill_qty) OVER (partition BY to_char(my_date,'yyyy')) "avg of refills by year"
    FROM scott.my_table x
    ORDER BY to_char(my_date, 'yyyymm')  
     
     
    Sadik likes this.