CODEWITHSUNDEEP
sql-serversum
Mitch
Mitch

Reputation: 2551

T SQL Sum from separate Views

I have 3 Views called vQuestion1, vQuestion2, vQuestion3. Each has 2 columns called Outcome and Total.

The Outcome column can have a number of values such as "N/A", "Improved", "Not Improved". Each Outcome has a total score.

vQuestion1                vQuestion2              vQuestion3
Outcome      Total        Outcome       Total     Outcome     Total
N/A          12           Improved      10        N/A         5
Improved     10           Not Improved  20        Improved    13
Not Improved 4

I need to create a result where the totals are added together to create a result as follows:

Total Answers
Outcome      Total
N/A          17
Improved     33
Not Improved 24

Please note that not all answers exist in each question, ie Question2 does not have N/A.

Upvotes: 0

Views: 150

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166396

First do a UNION ALL and then a SUM

Something like

SqlServer 2005+

;WITH Vals AS (
        SELECT  *
        FROM    vQuestion1
        UNION ALL
        SELECT  *
        FROM    vQuestion2
        UNION ALL
        SELECT  *
        FROM    vQuestion3
)
SELECT  Outcome,
        SUM(Total) Total
FROM    Vals
GROUP BY    Outcome

Or

SELECT  Outcome,
        SUM(Total) Total
FROM    (
            SELECT  *
            FROM    vQuestion1
            UNION ALL
            SELECT  *
            FROM    vQuestion2
            UNION ALL
            SELECT  *
            FROM    vQuestion3
        ) Vals
GROUP BY    Outcome

Upvotes: 1

Related Questions