CODEWITHSUNDEEP
sqlsql-server
Tobias Raphael Dieckmann
Tobias Raphael Dieckmann

Reputation: 492

Combine Row Values to "Others"

I have a maybe really tricky question. I have a table with costs, very simple: 

Cost name, cost value

And I want to output the TOP 10 costs values with the name, which is no problem, BUT: as 11th row, I want to output all others sum as "Others" marked ... Is that possible with a SQL query?

I hope you understand my question and I'm very thankful for all helpful answers.

Best regards,

Tobias

UPDATE:

enter image description here

<< Data Example >>

Upvotes: 1

Views: 75

Answers (3)

huMpty duMpty
huMpty duMpty

Reputation: 14470

;with CTE as(SELECT Name ,
value ,row_number() OVER (ORDER BY Name) AS RowNumber FROM #Temp )

select Name,value 
from cte
Where RowNumber<11
union
select 'Other',Sum(value)
from cte
Where RowNumber>=11

Sample Fiddle

Upvotes: 1

TechDo
TechDo

Reputation: 18649

Please try:

;with T as(
    select *, ROW_NUMBER() over (order by value desc) RNum
    from YourTable
)
select value, name from T 
where RNum<=10

union all

select sum(value), 'Others'
from T
where RNum>10

Upvotes: 3

Chrisky
Chrisky

Reputation: 577

Perhaps something like this?

select * from (select top (10) name, value from costs order by value) s1
UNION (
   select 'other', sum(value) from costs 
   where name not in (select top 10 Name from costs order by value)
)

This assumes Name is a PK on costs.

Upvotes: 1

Related Questions