CODEWITHSUNDEEP
sqlsql-serverdatepivot
Naru Lopo
Naru Lopo

Reputation: 19

pivot group by year

How can I group a pivot by the years?

SELECT YEAR(bolfecha) ,pvt.[C],PVT.[P]
FROM 
(select bolfecha, bolestado
from Tbboleta) as src

PIVOT
(
COUNT(bolestado)
FOR bolestado IN ([C], [P])
) AS pvt;

Bolfecha it's the column that have the dates it's like 2005-02-01 Using this code give me every year instead of grouping them only by year 2005, then all the registers for 2006, and so on...

I'm trying to coun how much 'C' and 'P' are registered on the column bolestado, and that works but I can't just group them by the year, help pls It gives me something like this

2005    53  3
2006    2   0
2006    2   0
2006    6   0

. . .

2005 it's the only one that actually grouped and I don't even know why Edit: Oh I know why 2005 it's the only one grouped it's because it only have 2005-12-31 in every 2005 row so that's why

Upvotes: 1

Views: 1648

Answers (1)

Mohammad
Mohammad

Reputation: 1990

Group your data using GROUP BY first then use PIVOT to turn row data into columns.

First, grouping as intended:

SELECT YEAR(bolfecha) [Year], bolestado, COUNT(*) ItemCount FROM Tbboleta
GROUP BY YEAR(bolfecha), bolestado

This produces this result:

Year  bolestado     ItemCount
2005          C     10
2005          P     7
2006          C     3
2006          P     8

Then now you can apply the pivot to this result-set by using:

SELECT * FROM
(
   SELECT YEAR(bolfecha) [Year], bolestado, COUNT(*) ItemCount FROM Tbboleta
   GROUP BY YEAR(bolfecha), bolestado
) RawData
PIVOT (
    MIN(ItemCount) FOR bolestado IN (C, P)
) PivotData

And this gives you this result:

Year     C      P
2005    10      7
2006     3      8

PIVOT can pick the values you want and create columns for these values based on a simple aggregate function (SUM, MIN, AVG, COUNT ...) but you can't group at the same time. If you need a group you need to do this separately using GROUP BY before doing the PIVOT.

Upvotes: 1

Related Questions