CODEWITHSUNDEEP
sql-serversql-server-2005pivot
yaqui
yaqui

Reputation: 99

SUM(Value) Group By After PIVOT

I have the following query for Pivot, that works nicely.

WITH PivotData AS 
( 
SELECT fechaHora, idWatimetro, semana, hora, minuto,dia,mes ,ano,valor
FROM   E_Registros_Watimetros 
) 
SELECT * 
FROM PivotData 
PIVOT(SUM(valor) FOR idWatimetro IN (
     [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
    ) ) AS P 
order by fechaHora, semana,dia,mes;

enter image description here

But now, I must be able to get Sum(valor) for every month or days or weeks or Year, but I´m trying without success, any help will be nice

Thank you for your time.

After suggestion of Pieter Geerkens, What I want to do is like this The Sum(1),Sum(2),Sum(3)... Sum(10) for every Month. The Month is an example, becuase this parameter can be, Year , day or weeks Sorry for not to be very clear, my english is not very good.

enter image description here

Upvotes: 2

Views: 415

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

You can do this with your query but you need to alter pivot source Query by remove the columns which not needed in final result. Try this.

WITH PivotData
     AS (SELECT idWatimetro,
                mes,
                ano,
                valor
         FROM   E_Registros_Watimetros)
SELECT *
FROM   PivotData
       PIVOT(Sum(valor)
            FOR idWatimetro IN ( [1],
                                 [2],
                                 [3],
                                 [4],
                                 [5],
                                 [6],
                                 [7],
                                 [8],
                                 [9],
                                 [10] ) ) AS P

Upvotes: 1

Code Different
Code Different

Reputation: 93161

You can add a second CTE to do the pivot and use the final SELECT to GROUP BY:

WITH PivotData AS 
( 
SELECT fechaHora, idWatimetro, semana, hora, minuto,dia,mes ,ano,valor
FROM   E_Registros_Watimetros where ano = 2012
), tempData AS (
SELECT *
FROM PivotData 
PIVOT(SUM(valor) FOR idWatimetro IN ([1],[2],[3],[4],[5]) ) AS P
)

SELECT MONTH(fechaHora) AS [Month],
        SUM([1]) AS KW1,
        SUM([2]) AS KW2,
        SUM([3]) AS KW3
FROM tempData
GROUP BY MONTH(fechaHora)

Edit: The above query works on my SQL Server 2008 box.

Upvotes: 1

Related Questions