Nihat
Nihat

Reputation: 59

Summarizing a column in SQL Server after the creation of Pivot table

I cannot summarize numbers in the table (SQL-Server) after pivoting and I will be very grateful for your advice.

Better if I explain the problem on the example:

Existing table:

    +-------+-----------+-----------+-------------------+
    |   #   |   $$$$$   |   Fire    |       Water       |
    +-------+-----------+-----------+-------------------+
    |   1   |   5       |       1   |   5               |
    |   1   |   4       |       1   |   5               |
    |   1   |   10      |       1   |   5               |
    |   2   |   3       |       3   |   8               |
    |   2   |   4       |       3   |   8               |
    +-------+-----------+-----------+-------------------+

Desired output:

+-------+-----------+-----------+-------------------+
|   #   |   $$$$$   |   Fire    |       Water       |
+-------+-----------+-----------+-------------------+
|   1   |   19      |       1   |   5               |
|   2   |   7       |       3   |   8               |
+-------+-----------+-----------+-------------------+

I tend to believe that I already tried all the solutions I found with summarizing and grouping by, but it was not solved, so I rely on you. Thanks in advance. The code I used to create the table:

WITH Enerc AS
(
    SELECT  
        a1.[#],
        a1.[$$$$$],
        a2.[cause_of_loss]          
    FROM 
        data1 AS a1
    LEFT JOIN 
        data2 AS a2 ON a1.[id] = a2.[id]
)
SELECT *
FROM Enerc
PIVOT 
    (SUM(gross_claim) FOR [cause_of_loss] IN ([Fire], [Water])) AS PivotTable;

Upvotes: 1

Views: 163

Answers (2)

claud.io
claud.io

Reputation: 1953

try group by after the pivot.

 With Enerc as
    (SELECT  
        a1.[#],
        a1.[$$$$$],
        a2.[cause_of_loss]

    from data1 as a1
left join data2 as a2
on a1.[id] = a2.[id]
)
select *
into tmp
from Enerc
PIVOT
(sum(gross_claim) 
FOR [cause_of_loss] in (
[Fire], [Water])) 
as PivotTable


select   [#], sum([$$$$$])as [$$$$$],  Fire,   Water 
from #tmp
group by [#],Fire, Water 

EDIT: in case of permission denied:

With Enerc as
    (SELECT  
        a1.[#],
        a1.[$$$$$],
        a2.[cause_of_loss]

    from data1 as a1
left join data2 as a2
on a1.[id] = a2.[id]
),phase2 as(
select *
from Enerc
PIVOT
(sum(gross_claim) 
FOR [cause_of_loss] in (
[Fire], [Water])) 
as PivotTable)
select  [#], sum([$$$$$])as [$$$$$],  Fire,   Water  
from phase2
group by [#],Fire, Water 

Upvotes: 0

StepUp
StepUp

Reputation: 38124

No need to pivot. Your desired result should be got by grouping and using SUM:

SELECT  
    a1.[#],
    SUM(a1.[$$$$$]),
    a1.[Fire]
    a1.[Water]  
from data1 as a1
group by  a1.[#],  a1.[Fire], a1.[Water]    

Let me show an example:

DECLARE @Hello TABLE
(
    [#] INT,
    [$$$$$] INT,
    [Fire] INT,
    [Water] INT
)

INSERT INTO @Hello
(
    #,
    [$$$$$],
    Fire,
    Water
)
VALUES
(   1, -- # - int
    5, -- $$$$$ - int
    1, -- Fire - int
    5  -- Water - int
    )
, (1, 4, 1, 5)
, (1, 10, 1, 5)
, (2, 3, 3, 8)
, (2, 4, 3, 8)

SELECT 
h.#, 
SUM(h.[$$$$$]),
h.Fire, 
h.Water 
FROM @Hello h
GROUP BY h.#, h.Fire, h.Water

Upvotes: 2

Related Questions