getting daily average of sales from monthly report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting daily average of sales from monthly report

Author

Topic

joeyym27
Starting Member

1 Post

Posted - 2014-10-09 : 04:34:28

Hi, 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

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).

SELECT sum(sf.ORDER_QNT)/31 as AVGPERDAY
FROM sales_fact sf,
date_dim dd
WHERE dd.date_id = sf.date_id
AND dd.MONTH_NUMBER = 1;

AVGPERDAY MONTH_NUMBER
---------- ------------
113.785714 1

but instead putting 31, I'd like to pull the totaldays from the actual_date using the Extract function so I try this

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"

**sorry about the code above, it looks nicely on the editor :(
can anyone help?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-09 : 09:35:38

I believe that EXTRACT is a MySql function. This is a SQL Server forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources