ORDER BY Date - Month/Year - Mixed Up Results 1

[CTOROCK]

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 methods.
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_TEST
GROUP BY TO_CHAR(MY_DATE, 'YYYY/MM')
ORDER BY TO_CHAR(MY_DATE, 'YYYY/MM')
/

The greatest risk, is not taking one.

 

[SantaMufasa]

CT,

There is nothing wrong with your SELECT statement...It should produce the output you want. Here is what I get:

SELECT TO_CHAR(MY_DATE, 'YYYY/MM') "Date",
SUM(REFILL_QTY) "Sum of Refills by Month"
FROM MY_TEST
GROUP BY TO_CHAR(MY_DATE, 'YYYY/MM')
ORDER BY TO_CHAR(MY_DATE, 'YYYY/MM');

Date    Sum of Refills by Month
------- -----------------------
2009/01                      25
2009/02                      30
2009/03                      23
2009/04                      29
2009/05                      33
2009/06                      37
2009/07                      39
2009/08                      31
2009/09                      35
2009/10                      29
2009/11                      23
2009/12                      27
2010/01                      15

13 rows selected.

Here is the adjustment to recevie Monthly average, as well:

SELECT TO_CHAR(MY_DATE, 'YYYY/MM') "Date",
SUM(REFILL_QTY) "Sum of Refills by Month",
avg(refill_qty) "Avg/Mo."
FROM MY_TEST
GROUP BY TO_CHAR(MY_DATE, 'YYYY/MM')
ORDER BY TO_CHAR(MY_DATE, 'YYYY/MM');

Date    Sum of Refills by Month    Avg/Mo.
------- ----------------------- ----------
2009/01                      25       12.5
2009/02                      30         15
2009/03                      23       11.5
2009/04                      29       14.5
2009/05                      33       16.5
2009/06                      37       18.5
2009/07                      39       19.5
2009/08                      31       15.5
2009/09                      35       17.5
2009/10                      29       14.5
2009/11                      23       11.5
2009/12                      27       13.5
2010/01                      15         15

13 rows selected.

To troubleshoot the problem you are having, could you please post the results of a SQL*Plus DESCRIBE of MY_TEST?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CTOROCK]

Thanks for the reply CT
However, I get this:

Date Sum of Refills by Month
------- ----------------------
2009/08 16279
2009/09 79989
2009/10 77748
2009/11 37265
2009/12 24968
2010/01 15187
2010/02 13029
2010/03 95260
2010/04 86845
2010/05 13405


So the months are out of order. Also, I was looking to get a total avg of all the months, could I get an average from the total from perhaps a rollup? Thanks again!


The greatest risk, is not taking one.

 

[Turkbear]

Hi,
Out of Order how?

Looks like
August September October november december 2009
followed by
January Feb, March, april,May 2010





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

So the months are out of order.

When you say the months are out of order, do you want to see January of 2010 appear prior to August of 2009? Are you looking for sums and averages for months regardless of year ?

I was looking to get a total avg of all the months

To avoid ambiguity, could you please give us an example of a total average of all the months?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CTOROCK]

I'm sorry, you are correct. I must have just been staring at the screen too long. However, any suggestions on the total monthly average? Thanks

The greatest risk, is not taking one.

 

[SantaMufasa]

However, any suggestions on the total monthly average?

So, are you looking for an average of the averages, to appear at the bottom of the listing?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Here is code for SQL*Plus that gives an average of the avergages:

set pages 500
set feedback off
col x heading "Date" format a7
col y heading "Sum of|Refills|by Month" format 99,999
col z heading "Avg/Mo." format 999.9
break on report
compute avg of z on report
SELECT TO_CHAR(MY_DATE, 'YYYY/MM') x,
SUM(REFILL_QTY) y,
avg(refill_qty) z
FROM MY_TEST
GROUP BY TO_CHAR(MY_DATE, 'YYYY/MM')
ORDER BY TO_CHAR(MY_DATE, 'YYYY/MM');

          Sum of
         Refills
Date    by Month Avg/Mo.
------- -------- -------
2009/01      535   267.5
2009/02       30    15.0
2009/03       23    11.5
2009/04      539   269.5
2009/05       33    16.5
2009/06       37    18.5
2009/07       39    19.5
2009/08      541   270.5
2009/09       35    17.5
2009/10       29    14.5
2009/11       23    11.5
2009/12       27    13.5
2010/01      525   525.0
                 -------
avg                113.1

Let us know if this works for you,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[CTOROCK]

Thanks Mufasa,

i was more interested in the average for the totals (sums)for each month. Hope this is a bit more clear. Thanks!

The greatest risk, is not taking one.

 

[CTOROCK]

got it:

compute avg of y on report.

thanks Mufasa, here's a star!

The greatest risk, is not taking one.