Add average column to Pivot Table SQL

Question

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.

Why dont you try putting this in cte or temptable and add extra column for what you need! — Kinchit Dalwani
– Kinchit Dalwani, Commented Mar 27, 2017 at 7:00
SELECT CandidateID, Name, [P1],[P2] ,([P1]+[P2])/2 as Average — Muhammad Nasir
– Muhammad Nasir, Commented Mar 27, 2017 at 7:06

Kinchit Dalwani · Accepted Answer · 2017-03-27 07:07:23Z

1

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

answered Mar 27, 2017 at 7:07

Kinchit Dalwani

3984 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JC Borlagdan Over a year ago

So if i have more than 2 AmbassadorID i will also add it manually? in this query Select CandidateID, Name, [P1],[P2],(([P1]+[P2])/2) as [Avg] from cte

JC Borlagdan Over a year ago

CAST((([P1] + [P2] + [P3])/3) AS FLOAT) AS AVG I added this, but still doesn't cast as float? why?

Kinchit Dalwani Over a year ago

Convert P1, P2, P3 column itself in the cte to numeric or in float.

Collectives™ on Stack Overflow

Add average column to Pivot Table SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related