How to PIVOT and calculating column average

Question

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 1

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

Answer 2

You could use group by instead of pivot:

select  Item
,       avg(case when datename(weekday, DateTime) = 'Sunday' then ActiveTime end) d1
,       avg(case when datename(weekday, DateTime) = 'Monday' then ActiveTime end) d2
,       avg(case when datename(weekday, DateTime) = 'Tuesday' then ActiveTime end) d3
,       avg(case when datename(weekday, DateTime) = 'Wednesday' then ActiveTime end) d4
,       avg(case when datename(weekday, DateTime) = 'Thursday' then ActiveTime end) d5
,       avg(case when datename(weekday, DateTime) = 'Friday' then ActiveTime end) d6
,       avg(case when datename(weekday, DateTime) = 'Saturday' then ActiveTime end) d7
,       avg(ActiveTime) AllDays
from    TableA
group by
        Item

Example at SQL Fiddle.