CODEWITHSUNDEEP
sql-servert-sql
Elham Azadfar
Elham Azadfar

Reputation: 739

How to get n rows to combine to one, and show other rows unchanged

I have this table

Col1        Col2
--------------------
1           100
2           50
3           60
4           70
5           20

I am trying to add multiple rows into one row with total For example if input is 2, result is a row that has sum of row1 to row2,and row3 to end row.

Here is what I want as results for my mock data:

Col1        Col2
--------------------
1           150(sum(row1 and row2)
2            60
3            70
4            20

Upvotes: 1

Views: 75

Answers (2)

Ari Singh
Ari Singh

Reputation: 1296

select min(col1), sum(col2) from table
where col1 is <= 2
UNION
select col1, col2 from table
where col1 is > 2

Note here though that col1 is not changed for rows 2 onwards.

Upvotes: 1

Ofir Winegarten
Ofir Winegarten

Reputation: 9365

you can GROUP BY with CASE and then add the row numbers with RANK or ROW_NUMBER

SELECT 
  ROW_NUMBER() OVER (ORDER BY col1), 
  col2 
FROM 
  (
    SELECT 
      CASE WHEN col1 <= 2 THEN 0 ELSE col1 END col1, 
      SUM([Col2]) col2 
    FROM 
      some_table 
    GROUP BY 
      CASE WHEN col1 <= 2 THEN 0 ELSE col1 END
  ) a

Upvotes: 3

Related Questions