Like PIVOT and middle column calculation

Question

I admitted that this is one of the most complex SQL statements that I have encountered so far. I tore off the wall on this and I hope someone 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 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 that the average Monday is 6 because of (10 + 2) / 2 days. On average there are only 3 on Tuesdays, because this happens on Tuesday only once. The average value for element 1 is 5, because at (10 + 2 + 3) / 3 = 5

For item 2:

This happens only once on Tuesday, so the average Tuesday for point 2 is 5. The average is 5 because it only happens once, so 5/1 = 5.

so far I have come up with the following SQL expression, the purpose of which is to show the average ActiveTime of each element, broken down on weekdays, as well as the total average ActiveTime for each element:

 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 result in the following:

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

So it almost works, but notices the value 4.5, it got that, doing (6 + 3) / 2, which is wrong, I don't want to just add the average value. Andybody can suggest an improvement to my SQL statement to calculate the average using the actual ActiveTime average of each element?

Answer 1

You can use avg() over() to get the result. This will allow you to split the data into each item :

 avg(ActiveTime) over(partition by item) Avg_Item 

Thus, the full query will look like this:

 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 can 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 in SQL Fiddle.