CODEWITHSUNDEEP
sqlsql-server
chibis
chibis

Reputation: 858

Display top three values and the sum of all other values

After I ORDER BY cnt DESC my results are

fld1  cnt
 A     9
 E     8
 D     6
 C     2
 B     2
 F     1

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.

Upvotes: 2

Views: 4663

Answers (4)

suff trek
suff trek

Reputation: 39777

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)

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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.

Upvotes: 2

heretolearn
heretolearn

Reputation: 6545

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

Upvotes: 0

danisius
danisius

Reputation: 597

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"

Upvotes: -2

Related Questions