CODEWITHSUNDEEP
sqlsql-servert-sqlrank
cmonti
cmonti

Reputation: 187

Grouping data on SQL Server

I have this table in SQL Server:

Year    Month       Quantity
----------------------------
2015    January     10
2015    February    20
2015    March       30
2014    November    40
2014    August      50

How can I identify the different years and months adding two more columns that group the same years with a number and then different months in sequential way like the example

Year    Month       Quantity    Group   Subgroup
------------------------------------------------
2015    January     10          1       1
2015    February    20          1       2
2015    March       30          1       3
2014    November    40          2       1
2014    August      50          2       2

Upvotes: 6

Views: 88

Answers (2)

Bulat
Bulat

Reputation: 6969

To associate group and subgroup with a number you can do this:

WITH RankedTable AS (
  SELECT year, month, quantity,
  ROW_NUMBER() OVER (partition by year order by Month) AS rn
  FROM yourtable)
SELECT year, month, quantity,
   SUM (CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (ORDER BY YEAR) as year_group, 
   rn AS subgroup
FROM RankedTable

Here ROW_NUMBER() OVER clause calculates rank of a month within a year. And SUM() ... OVER calculates running SUM for the months with rank 1.

SQL Fiddle

Upvotes: 0

Amit
Amit

Reputation: 46323

You can use DENSE_RANK to calculate the groups for you:

SELECT t1.*, DENSE_RANK() OVER (ORDER BY Year DESC) AS [Group],
  DENSE_RANK() OVER (PARTITION BY Year ORDER BY DATEPART(month, Month + ' 01 2010')) AS [SubGroup]
FROM t1
ORDER BY 4, 5

See this fiddle.

Upvotes: 5

Related Questions