CODEWITHSUNDEEP
sqlsql-server
JC Borlagdan
JC Borlagdan

Reputation: 3618

Add average column to Pivot Table SQL

The following is my table t_Vote:

| CandidateID | AmbassadorID  | Rate |
|      A      |       P1      |  6   |
|      A      |       P2      |  3   |
|      B      |       P1      |  5   |
|      B      |       P2      |  8   |

After doing a Pivot this is my output:

|CandidateID | P1 | P2 |
------------------------
|     A      | 6  | 3  |
|     B      | 5  | 8  |

What I want to achieve is the following table:

|CandidateID | P1 | P2 | Average |
----------------------------------
|     A      | 6  | 3  |   4.5   |
|     B      | 5  | 8  |   6.5   |

Here is my query in doing the pivot:

SELECT CandidateID, Name, [P1],[P2]
FROM
(
    SELECT Name, AmbassadorID, CandidateID, Rate
    FROM t_Vote INNER JOIN t_Candidate
    ON t_Vote.CandidateID = t_Candidate.ID
) SRC
PIVOT
(
    AVG(Rate)
    FOR AmbassadorID IN ([P1],[P2])
) AS nT

P.S. I removed some of the columns in the question just to avoid confusion.

Upvotes: 1

Views: 191

Answers (1)

Kinchit Dalwani
Kinchit Dalwani

Reputation: 398

You can go for this:

 ;With cte as
  (
    SELECT CandidateID, Name, [P1],[P2]
    FROM
    (
      SELECT Name, AmbassadorID, CandidateID, Rate
      FROM t_Vote INNER JOIN t_Candidate
      ON t_Vote.CandidateID = t_Candidate.ID
    ) SRC
   PIVOT
    (
      AVG(Rate)
      FOR AmbassadorID IN ([P1],[P2])
    ) AS nT
  )

 Select CandidateID, Name, [P1],[P2],(([P1]+[P2])/2) as [Avg] from cte

You can do the same by using temp table. You can find more about cte on: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Upvotes: 1

Related Questions