SQL/OLAP Query (average over a particular weekday)

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

SQL/OLAP Query (average over a particular weekday)

Author

Topic

nici
Starting Member

9 Posts

Posted - 2013-06-21 : 09:43:14

Hi all,

I'm struggling with the following query.

I'm trying to get the average sale form each Saturday in year 2005 and in the end the average from all Saturdays in 2005.

This is how it's supposed to look like

OrderDate AverageSale
01.01.2005 1857,12
08.01.2005 1754,25
10.01.2005 1539,86
... ...
... ...
17.12.2005 1754,57
24.12.2005 2076,16
30.12.2005 1829,79
ALL 1947,64

I already got this, which shows me each Saturday with it's average Sale except the last line.

SELECT OrderDate
AVG(CAST((ProductPrice * OrderQuantity) AS MONEY)) AS AverageSale

FROM TEST3
WHERE DATENAME(WEEKDAY, OrderDate) = 'Saturday' AND
Year(orderdate) = 2005
GROUP BY (OrderDate)

Does some know how to get the last line with the average sale form all Saturdays in 2005?? Perhaps with a OLAP function?

Thanks in advance for any advice!!

Cheers
anna

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-21 : 10:05:33

Use grouping sets like this:

SELECT  OrderDate ,
        AVG(CAST(( ProductPrice * OrderQuantity ) AS MONEY)) AS AverageSale
FROM    TEST3
WHERE   DATENAME(WEEKDAY, OrderDate) = 'Saturday'
        AND YEAR(orderdate) = 2005
GROUP BY GROUPING SETS(OrderDate,());

While you are at it, you might also want to change the WHERE clause like shown below - that makes it language independent, i.e., you are not relying on the English word 'Saturday' if you do it like shown below

WHERE   DATEDIFF(dd,0,OrderDate)%7 = 5
        AND YEAR(orderdate) = 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 10:05:38

[code]
SELECT OrderDate
AVG(CAST((ProductPrice * OrderQuantity) AS MONEY)) AS AverageSale
FROM TEST3
WHERE DATEDIFF(dd,0,OrderDate)%7 = 5 AND
orderdate > = '20050101' AND orderDate < '20060101'
GROUP BY (OrderDate)
WITH ROLLUP
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

nici
Starting Member

9 Posts

Posted - 2013-06-24 : 12:41:51

Thanks guys !!
I only made some small changes.
Both queries are working and looking pretty nice.

SELECT
CASE
WHEN GROUPING (OrderDate)= 1
THEN 'All'
ELSE CAST(OrderDate AS CHAR(50))
END AS OrderDate,
AVG(CAST((ProductPrice * OrderQuantity) AS MONEY)) AS Umsatz
FROM [u528637].[dbo].[TEST3]
WHERE DATEDIFF(dd,0,OrderDate)%7 = 0 AND
orderdate > = '2008-01-01' AND orderDate < '2009-01-01'
GROUP BY (OrderDate)
WITH ROLLUP

SELECT
CASE
WHEN GROUPING (OrderDate)= 1
THEN 'All'
ELSE CAST(OrderDate AS CHAR(50))
END AS OrderDate,
AVG(CAST((ProductPrice * OrderQuantity) AS MONEY)) AS Umsatz
FROM [u528637].[dbo].[TEST3]
WHERE DATENAME(WEEKDAY, OrderDate) = 'Montag' AND
Year(orderdate) = 2008
GROUP BY
GROUPING SETS ((),[OrderDate])

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 12:51:56

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 12:51:57

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources