Title: Getting average per day using the extract and last_day functions
Tags: sql;oracle11g
Question: need a quick help here
I have this small project, I have this report that have the total of order along with the date of the order (This a continuation from my other question actually)
So here's what I've been doing
SELECT  sf.ORDER_QNT, dd.ACTUAL_DATE, dd.MONTH_NUMBER
FROM    sales_fact sf,
date_dim dd
WHERE   dd.date_id = sf.date_id
AND dd.MONTH_NUMBER = 1;
ORDER_QNT          ACTUAL_DATE MONTH_NUMBER
-------------------------------------------
1100             05/01/13            1
100             05/01/13            1
140             06/01/13            1
110             07/01/13            1
200             08/01/13            1
500             08/01/13            1
230             08/01/13            1
500             08/01/13            1
200             08/01/13            1
53             15/01/13            1
53             22/01/13            1
Now, I want to get the average for that month (average per day) by using the last_day function.
SELECT  sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(to_date('05/01/13','dd/mm/rr'))) as AVGPERDAY,
dd.month_number
FROM    sales_fact sf,
date_dim dd
WHERE   dd.date_id = sf.date_id
AND     dd.month_number = 1
GROUP BY dd.month_number;
AVGPERDAY    MONTH_NUMBER
--------------------------
113.785714        1
The result is nice, but now when I change the date with the dd.actual_date it gives error
SELECT  sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(dd.actual_date)) as AVGPERDAY,
dd.month_number
FROM    sales_fact sf,
date_dim dd
WHERE   dd.date_id = sf.date_id
AND     dd.month_number = 1
GROUP BY dd.month_number;
Error at Command Line : 1 Column : 53
Error report -
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
can anyone help?

Here is the accepted answer: sum(sf.ORDER_QNT) / decode(round(max(dd.actual_date) - min(dd.actual_date)), 0, 1)
You may use this expression in your query (but the result may be not the same as with LAST_DAY)
Or you may make EXTRACT(DAY FROM LAST_DAY(dd.actual_date)) the part of a GROUP BY:
SELECT  sum(sf.ORDER_QNT)/EXTRACT(DAY FROM LAST_DAY(dd.actual_date)) as AVGPERDAY,
dd.month_number
FROM    sales_fact sf,
date_dim dd
WHERE   dd.date_id = sf.date_id
AND     dd.month_number = 1
GROUP BY dd.month_number, EXTRACT(DAY FROM LAST_DAY(dd.actual_date));


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:10/09/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><oracle11g>', 'Title': 'Getting average per day using the extract and last_day functions', 'ViewCount': 41, 'id': 26273440, 'user_AboutMe': '', 'user_AccountId': 5144602.0, 'user_DisplayName': 'joeyy27', 'user_Reputation': 21.0, 'user_Views': 10.0, 'user_id': 4120940.0}}