CODEWITHSUNDEEP
sqlsqlite
Vedad
Vedad

Reputation: 223

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?

Upvotes: 0

Views: 168

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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.

One ) was missing I addet it, the code above does the Job :)

Upvotes: 1

Related Questions