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

Source (Stackoverflow)
Comments