CODEWITHSUNDEEP
sqlsql-server-2008
Fizor
Fizor

Reputation: 1530

Rolling up remaining rows into one called "Other"

I have written a query which selects lets say 10 rows for this example. 

+-----------+------------+
| STORENAME | COMPLAINTS |
+-----------+------------+
| Store1    |          4 |
| Store7    |          2 |
| Store8    |          1 |
| Store9    |          1 |
| Store2    |          1 |
| Store3    |          1 |
| Store4    |          1 |
| Store5    |          0 |
| Store6    |          0 |
| Store10   |          0 |
+-----------+------------+

How would I go about displaying the TOP 3 rows BUT Having the remaining rows roll up into a row called "other", and it adds all of their Complaints together?

So like this for example:

+-----------+------------+
| STORENAME | COMPLAINTS |
+-----------+------------+
| Store1    |          4 |
| Store7    |          2 |
| Store8    |          1 |
| Other     |          4 |
+-----------+------------+

So what has happened above, is it displays the top3 then adds the complaints of the remaining rows into a row called other

I have exhausted all my resources and cannot find a solution. Please let me know if this makes sense.

I have created a SQLfiddle of the above tables that you can edit if it is possible :)

Here's hoping this is possible :)

Thanks, Mike

Upvotes: 2

Views: 39

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

I do this with double aggregation and row_number():

select (case when seqnum <= 3 then storename else 'Other' end) as StoreName,
       sum(numcomplaints) as numcomplaints
from (select c.storename, count(*) as numcomplaints,
             row_number() over (order by count(*) desc) as seqnum
      from complaints c
      where c.complaint = 'Yes'
      group by c.storename
     ) s
group by (case when seqnum <= 3 then storename else 'Other' end) ;

From what I can see, you don't really need any additional information from stores, so this version just leaves that table out.

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Something like this may work

select *,    row_number() over (order by complaints desc) as sno
into #temp
from
(
SELECT 
  a.StoreName
  ,COUNT(b.StoreID) AS [Complaints]
FROM Stores a
LEFT JOIN 
(
  SELECT 
    StoreName
    ,Complaint
  ,StoreID
  FROM Complaints
  WHERE Complaint = 'yes') b on b.StoreID = a.StoreID


GROUP BY a.StoreName
) as t ORDER BY [Complaints] DESC

select storename,complaints from #temp where sno<4
union all
select 'other',sum(complaints) as complaints from #temp where sno>=4

Upvotes: 1

Related Questions