Vedad Vedad - 3 months ago 6
SQL Question

SQL get top 10 and the rest in two columns

I have question to a SQL query where I cant manage to come forward. I want to take the

SUM
of the top 10 values of a table as one column and the
SUM
of the rest as another to make a PIE graph which shows how many percentage the top10 takes in the whole:

I can for now get the rest or the top10 but not booth in one row:

SELECT
SUM(counted) as Rest
FROM
Northeast
WHERE
counted < (SELECT counted
FROM Northeast
ORDER BY counted DESC
LIMIT 10)


Any solution?

Answer

Most databases support row_number(), so you can do:

select sum(case when seqnum <= 10 then counted end) as top10_counted,
       sum(case when seqnum > 10 then counted else 0 end) as rest
from (select ne.*,
             row_number() over (order by counted) as seqnum
      from northeast ne
     ) ne;

EDIT:

In SQLite, you can do:

select (select sum(counted) 
        from (select ne.* from northeast ne order by counted desc limit 10)
       ) as top10,
       (select sum(counted)
        from northeast
        where counted not in (select counted from northeast order by counted desc limit 10
       ) as rest;

This works, assuming you have no ties in the data.

Comments