Display top three values and the sum of all other values

Question

After I ORDER BY cnt DESC my results are

I need to have top 3 displayed and the rest to be summed as 'other', like this:

fld1  cnt
A      9
E      8
D      6
other  5

EDITED:

Thank you all for your input. Maybe it will help if you see the actual statement:

SELECT 
    CAST(u.FA AS VARCHAR(300)) AS FA,
    COUNT(*) AS Total,
    COUNT(CASE WHEN r.RT IN (1,11,12,17) THEN r.RT END) AS Jr,
    COUNT(CASE WHEN r.RT IN (3,4,13) THEN r.RT END) AS Bk,
    COUNT(CASE WHEN r.RT NOT IN (1,11,12,17,3,4,13) THEN r.RT END ) AS Other
FROM R r
    INNER JOIN DB..RTL rt
    ON r.RT = rt.RTID
    INNER JOIN U u
    ON r.UID = u.UID
WHERE rt.LC = 'en' 
GROUP BY CAST(u.FA AS VARCHAR(300))--FA is ntext
ORDER BY Total DESC

The produced result has 19 records. I need to show the top 5 and sum up the rest as "Other FA". I don't want to do a select from a select from a select with this kind of statement. I am more looking for some SQL function. Maybe ROW_NUMBER is good idea, but I don't know how exactly to apply it in this case.

You may want to look at the accepted solution here: http://stackoverflow.com/questions/14478361/sum-with-sql-server-rollup-but-only-last-summary — PM 77-1, Aug 30 '13 at 19:53
Your result set has 2 fields and your query has 5, and non of the fields of 1 are present in the other :P So I am unsure of the relation between both now... — asontu, Aug 30 '13 at 20:47
You could adapt my solution if you replace `FROM @MyTable x` with a derivated table thus: `FROM (your query without ORDER BY) x` plus other changes. — Bogdan Sahlean, Aug 30 '13 at 20:48
The result set has also 5 fields. The initial 2 that I should are the ones that matter. I need the top 5 from the Total field — chibis, Aug 30 '13 at 20:53

Yuriy Galanter · Answer 1 · 2013-08-30T21:09:08.403

4

Could be something like this:

select top 3 fld1, cnt from mytable
union
select 'Z - Other', sum(cnt) from mytable
where fld1 not in (select top 3 fld1 from mytable order by fld1)
order by fld1

(Updated to include order by)

edited Aug 30 '13 at 21:09

answered Aug 30 '13 at 20:13

Yuriy Galanter

38,833
15
69
136

Yup, thought about this as well, but kept with OP structure. I'd add ORDER BY too – Yuriy Galanter Aug 30 '13 at 20:37
@KirkBroadhurst: "super succint" **doens't means** (always) the best solution. – Bogdan Sahlean Aug 30 '13 at 20:41
@BogdanSahlean agree, and I voted for your answer too, but I like this better. Easier to read, and less nested queries. If the ordering is important use a CTE. – Kirk Broadhurst Aug 30 '13 at 20:53
@KirkBroadhurst: i-one has signaled **a small** problem with this solution: all those SELECT TOP(3) ... FROM mytable doesn't have an ORDER BY. – Bogdan Sahlean Aug 30 '13 at 20:56
OK, I will add them just to avoid confusion – Yuriy Galanter Aug 30 '13 at 21:02
The logical order of execution is UNION and then ORDER BY. First SELECT needs an ORDER BY before UNION. – Bogdan Sahlean Aug 30 '13 at 21:32
Not necessarily if the second select guaranteed to be sorted last. In that example I artificially set name to "Z..." but there're other means (additional sort field etc) – Yuriy Galanter Aug 30 '13 at 21:34

Gordon Linoff · Accepted Answer · 2013-08-30T20:59:44.493

I think the most direct way is to use row_number() to enumerate the rows and then reaggreate them:

select (case when seqnum <= 3 then fld1 else 'Other' end) as fld1,
       sum(cnt) as cnt
from (select t.*, row_number() over (partition by fld1 order by cnt desc) as seqnum
      from t
     ) t
group by (case when seqnum <= 3 then fld1 else 'Other' end);

You can actually do this as part of your original aggregation as well:

select (case when seqnum <= 3 then fld1 else 'Other' end) as fld1,
       sum(cnt) as cnt
from (select fld1, sum(...) as cnt,
             row_number() over (partition by fld1 order by sum(...) desc) as seqnum
      from t
      group by fld1
     ) t
group by (case when seqnum <= 3 then fld1 else 'Other' end);

EDIT (based on revised question):

select (case when seqnum <= 3 then FA else 'Other' end) as FA,
       sum(Total) as Total
from (SELECT CAST(u.FA AS VARCHAR(300)) AS FA,
             COUNT(*) AS Total,
             ROW_NUMBER() over (PARTITION BY CAST(u.FA AS VARCHAR(300)) order by COUNT(*) desc
                               ) as seqnum
      FROM R r
          INNER JOIN DB..RTL rt
          ON r.RT = rt.RTID
          INNER JOIN U u
          ON r.UID = u.UID
      WHERE rt.LC = 'en' 
      GROUP BY CAST(u.FA AS VARCHAR(300))--FA is ntext
     ) t
group by (case when seqnum <= 3 then FA else 'Other' end)
order by max(seqnum) desc;

The final order by keeps the records in ascending order by total.

Please see my edit. Can you help me fit ROW_NUMBER() into that statement. Thank you. — chibis, Aug 30 '13 at 20:50

score 0 · Answer 3 · answered Aug 30 '13 at 20:27

0

You could try something like this:

select fld1,cnt from test
where cnt in(select top 3 cnt from test)
union
select 'Other', sum(cnt)from test
where cnt not in (select top 3 cnt from test)
order by cnt desc;

SQLFiddle

answered Aug 30 '13 at 20:27

heretolearn

6,387
4
30
53

score -2 · Answer 4 · answered Aug 30 '13 at 19:58

I`m not sure how "first" and skip clauses should be used in SQLServer but in firebird this works but i think it could be modified to run on SQLServer

    select first 3 p.fld1,p.cnt from Table p
    union
    select t."others",sum(t.cnt )  from (
    select skip 3 'others' as "others",p.cnt from Table p
    ) as t
    group by "others"

Display top three values and the sum of all other values

4 Answers4