Need help with a SQL/OLAP Query (average of a particular weekday over the year)

• anna_magdalena_guenther

  Grasshopper

  Points: 22

  More actions

  June 21, 2013 at 7:33 am

  #294962

  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

• Jason-299789

  SSC-Insane

  Points: 21601

  More actions

  June 21, 2013 at 7:43 am

  #1626320

  You might want to look at the GROUPING SETS functionality or ROLLUP/CUBE grouping for this.

  http://msdn.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

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

  _________________________________________________________________________
  SSC Guide to Posting and Best Practices