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?
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download