SQL Get the Average of the Column Values of a Pivot

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi There,

Having trouble with SQL pivot. below is the actual result i get when i ran the query.

Name | Average | 7/24/2014 | 7/26/2014    | 7/27/2014      | 7/28/2014    | 7/31/2014
Test | -----------| NULL      | 00:06:20:000 |	00:06:24:000 | 00:06:25:000 | 00:06:24:000

Now, what i want to know is how do i add the values of each column and compute show the average.
below if the desired output.

Name | Average | 7/24/2014 | 7/26/2014    | 7/27/2014      | 7/28/2014    | 7/31/2014
Test | 00:06:23:000| NULL      | 00:06:20:000 |  00:06:24:000 | 00:06:25:000 | 00:06:24:000

below is the code of Pivot

SQL

BEGIN
     SET @query = ' SELECT * FROM(SELECT p.[Name]
     , ''-----------'' AS Average 
     , CONVERT(varchar, DATEADD(ms, AVG(p.CallLengthSec) * 1000, 0), 114) AS CallLengthSumMin
     , p. ' + @dategroup + ' 
FROM #prod p WHERE p.EmpInfoID IN (' + @ProfGroupIDs + ') ' + @withinshift + ' 

GROUP BY p.Name, p. ' + @dategroup + '   ) x
PIVOT ( MAX(CallLengthSumMin) FOR ' + @dategroup + ' IN (' + @cols + ') ) p  
ORDER BY p.Name ASC '
END

Thanks, in advance. Thanks

Posted 12-Aug-14 9:25am

lgmanuel

Updated 12-Aug-14 10:27am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-08-12T10:52:00

Solution 1

I asssume that you wat to get average of CallLengthSumMin for each date and average for entire column:

date1    average
date2    average
date3    average
...
dateN    average
-----    total average

Then you want to diplay data as a pivot table.

If i'm not wrong, please refer this[^]

Posted 12-Aug-14 10:52am

Maciej Los

Comments

lgmanuel 12-Aug-14 19:06pm

Thanks for the reply,

Yes, exactly. i already have the average per date. now i'm not sure how to average the whole column.

The link you have provided seems to point only google main page and no article.

Maciej Los 13-Aug-14 7:07am

The link works perfect for me. Yes, it link to Google with this sentence: sql+pivot+add+total+column. Try it. There are tons of examples.

SQL Get the Average of the Column Values of a Pivot

1 solution

Solution 1

Add your solution here

Preview 0

SQL Get the Average of the Column Values of a Pivot

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us