Fylix Fylix - 3 months ago 7
SQL Question

How to PIVOT and calculating column average

I admitted this is one of the most complex SQL statement I have to face so far. I sorta hit the wall on this one and I hope somebody can give me a hand.

I have this table in the database

Item ActiveTime(sec) DateTime
-------------------------------------------
1 10 2013-06-03 17:34:22 -> Monday
2 5 2013-06-04 17:34:22 -> Tuesday
1 2 2013-06-03 12:34:22 -> Monday
1 3 2013-06-04 17:33:22 -> Tuesday


I want it to look this way after my SQL Statement

Item Mon Tues Wed Thurs Fri Sat Sun Average
-----------------------------------------------------------------------------------
1 6 3 5
2 5 5


How it works

For Item 1:



You can see Monday average is 6 due to (10 + 2) / 2 days
Tuesday average is just 3 because it occurs on Tuesday only once.
Average value for item 1 is 5 because on (10 + 2 + 3)/3 = 5

For Item 2:



It occurs only once on Tuesday so the average for Tuesday for Item 2 is 5.
Average is 5 because it only happens once so 5/1 = 5.

so far I came up with the following SQL Statement which aims to show the average ActiveTime of each Item broken down by weekday as well as the overall average ActiveTime for each item:

Select *,((ISNULL([Sunday],0) +ISNULL([Monday],0)+ ISNULL([Tuesday],0)+
ISNULL([Wednesday],0)+ ISNULL([Thursday],0)+ISNULL([Friday],0)+
ISNULL([Saturday],0)) /
( CASE WHEN [Sunday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Monday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Tuesday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Wednesday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Thursday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Friday] is null
THEN 0 ELSE 1 END +
CASE WHEN [Saturday] is null
THEN 0 ELSE 1 END )) as Avg
FROM ( SELECT * FROM
(
SELECT a.ResetTime as ResetTime,a.ApartmentDescription as Apartment,
DATENAME(WEEKDAY,a.DateTime) _WEEKDAY
FROM tblECEventLog a
)
AS v1 PIVOT (AVG(ResetTime) FOR _WEEKDAY IN
([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday], [Saturday])
)
AS v2
)
AS v3


Running the above SQL will yield the following:

Item Mon Tues Wed Thurs Fri Sat Sun Average
-----------------------------------------------------------------------------------
1 6 3 4.5
2 5 5


So it almost works but notice the value 4.5, it got that by doing (6+3)/2 which is incorrect, I don't want to just add the average. Andybody can suggest improvement to my SQL statement to have the Average calculate using the actual average ActiveTime for each item?

Answer

You should be able to use avg() over() to get the result. This will allow you to partition the data by each item:

avg(ActiveTime) over(partition by item) Avg_Item

So the full query will be:

SELECT item,
  [Sunday],
  [Monday],
  [Tuesday],
  [Wednesday],
  [Thursday],
  [Friday],
  [Saturday],
  Avg_Item
FROM 
( 
  SELECT a.ActiveTime as ActiveTime,a.Item as  Item,DATENAME(WEEKDAY,a.DateTime) _WEEKDAY,
    avg(ActiveTime) over(partition by item) Avg_Item
  FROM TableA a  
) AS v1 PIVOT 
(
  AVG(ActiveTime) 
  FOR _WEEKDAY IN 
(
  [Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
) AS v2;

See SQL Demo

Comments