CODEWITHSUNDEEP
sqlsql-serverdatabase
RussianBear7
RussianBear7

Reputation: 51

Reducing database rows for aggregation

I have table:

+ID+Duration+Cost_change+Def_cost+ | 1| 0.10 | 0.191 | 13.700 | | 2| 0.15 | 0.291 | 21.200 | | 3| 0.14 | 0.043 | 17.500 | | 4| 0.10 | 2.131 | 19.000 | | 5| 0.10 | 3.000 | 11.000 | | 6| 0.19 | 1.100 | 18.500 | | 7| 0.10 | 0.000 | 14.000 | | 8| 0.10 | 0.000 | 13.000 | | 9| 0.18 | 0.000 | 15.000 | |10| 0.10 | 4.100 | 12.000 | |11| 0.14 | 0.000 | 9.800 | |12| 0.10 | 0.000 | 7.000 | +---------+-------+--------+

I have an extra column(groups) which groups values by 3 rows 1,1,1,2,2,2,3,3,3,4,4,4 and so on. Later I'll have to aggregate(SUM,AVG) values by these groups. Aim - to reduce number of rows in 3 times (without losing data). Will be grateful for any advise.

Expect:

+ID+Duration+Cost_change+Def_cost+Groups | 1| 0.10 | 0.191 | 13.700 | 1 | 2| 0.15 | 0.291 | 21.200 | 1 | 3| 0.14 | 0.043 | 17.500 | 1 | 4| 0.10 | 2.131 | 19.000 | 2 | 5| 0.10 | 3.000 | 11.000 | 2 | 6| 0.19 | 1.100 | 18.500 | 2 | 7| 0.10 | 0.000 | 14.000 | 3 | 8| 0.10 | 0.000 | 13.000 | 3 | 9| 0.18 | 0.000 | 15.000 | 3 |10| 0.10 | 4.100 | 12.000 | 4 |11| 0.14 | 0.000 | 9.800 | 4 |12| 0.10 | 0.000 | 7.000 | 4 +---------+-------+--------+

Simple sequence by 3 values.

I'm using SQL Server 2012

Upvotes: 1

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

This is a bit too long for a comment.

But, if you want to add the column groups, you can just do:

select 1 + ((id - 1) / 3) as groups

If the id values could have holes, use row_number() instead:

select 1 + (row_number() over (order by id) - 1) / 3 as groups

SQL Server does integer division, so this assigns the values as you have specified.

Upvotes: 1

Related Questions