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

How to PIVOT and calculating column average

For Item 1:

For Item 2:

Sign up