CODEWITHSUNDEEP
sqlsql-server
user2513497
user2513497

Reputation: 11

Dynamic grouping of data|| SQL query needed

Year        Salary
2014        5000
2015        6500
2016        6800
2017        6900
2018        7000
2019        8000

I want to group data dynamically. Suppose if i want group of 2 then data should be grouped in 3 groups of 2 years each Resultset as :

Year          Salary
2014-2015     11500
2016-2017     13300
2018-2019     15000

If i want group of 3 then, 

Year.            Salary
2014-2016        18300
2017-2019        21900

No hardcoding

Upvotes: 0

Views: 251

Answers (3)

spioter
spioter

Reputation: 1870

Option 1 use division by your number of years and round down to devise nested function calls that return the YearGroup desired. This could get messy but it's possible

Option 2 - the most flexible across any sql platform would be to add a YearGroup table (or view) with one column per desired group 

Year | GroupOne  | GroupTwo | GroupThree
-----+-----------+----------+-----------
2019 | 2019.1    | 2018.2   | 2019.3
2018 | 2018.1    | 2018.2   | 2016.3
2017 | 2017.1    | 2016.2   | 2016.3
2016 | 2016.1    | 2016.2   | 2016.3
2015 | 2015.1    | 2014.2   | 2015.3
...

The decimals are optional but good to let people know number of years in group though I guess true third normal form would be one table per years grouped

Option 2 has the added benefit that anyone can select from the YearGroup table(s) to see the mapping first hand

You could also add groups that have different anchor years like group two years with odd as first year(merge 2017 and 2018) vs even as first year (merge 2018 with 2019)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can do this using window functions and aggregation. Assuming you want n years in each group:

select min(year), max(year), sum(salary)
from (select t.*, min(year) over () as min_year
      from t
     ) t
group by floor((year - min_year) / @n)
order by min(year);

Upvotes: 3

M. Kanarkowski
M. Kanarkowski

Reputation: 2205

For example you can create a few variables that will help you in creating groups within the table i.e. 2014-2015 etc. Adjusting @RowsToGroup you can group as many rows as you want. @remainder helps to find next groups within Year. In the CTE I used sum as a window function plus case to create a column to group by.

create table #years (Year int,        Salary int )
insert into #years values (2014        ,5000)
insert into #years values (2015        ,6500)
insert into #years values (2016        ,6800)
insert into #years values (2017        ,6900)
insert into #years values (2018        ,7000)
insert into #years values (2019        ,8000)

declare @minYear int = (select min(Year) from #years);
declare @RowsToGroup int = 2;
declare @remainder decimal(12,6) = (select @minYear % @RowsToGroup) ;

with YearsToGroup as (
select 
     *
    ,sum(case when Year % @RowsToGroup = @remainder then 1 else 0 end) over (order by Year asc) as grouping
from #years
)
select 
    cast(min(Year) as varchar(10)) + '-' + cast(max(Year) as varchar(10)) as years
    ,sum(salary) as salary
from YearsToGroup
group by grouping

Results for @RowsToGroup = 2:

years       salary
2014-2015   11500
2016-2017   13700
2018-2019   15000

Results for @RowsToGroup = 3:

years       salary
2014-2016   18300
2017-2019   21900

Upvotes: 0

Related Questions