SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 SQL/OLAP Query (average over a particular weekday)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nici
Starting Member

9 Posts

Posted - 06/21/2013 :  09:43:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 06/21/2013 :  10:05:33  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  10:05:38  Show Profile  Reply with Quote

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


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

Edited by - visakh16 on 06/21/2013 10:07:22
Go to Top of Page

nici
Starting Member

9 Posts

Posted - 06/24/2013 :  12:41:51  Show Profile  Reply with Quote

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])
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/24/2013 :  12:51:56  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/24/2013 :  12:51:57  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000