CODEWITHSUNDEEP
sqlsqlitegroup-by
Natalie Perret
Natalie Perret

Reputation: 8997

How to compute a sum based on the 2 biggest values for each group (group by)?

Let's say, I have this table below:

| Name     | Voters 
| George   | 10     
| Barack   | 20   
| Barack   | 50
| Barack   | 40
| Donald   | 30     
| Bill     | 20     
| George   | 10     
| Bill     | 30     
| Bill     | 15      
| Bill     | 10

I would like to get the sum of the 2 biggest numbers of Voters for each Name(or less) 

| Name     | Sum2BiggestVoters 
| George   | 20 (10 + 10)
| Donald   | 30 (30)     
| Bill     | 20 (30 + 20; 10 and 15 are not considered not part of the 2 biggest numbers of voters for Bill)
| Barack   | 90 (50 + 40; 20 is not considered here for the same reason as above)

It looks a bit like this post: How to get summation with count larger than certain amount, except that I am using SQLite which lacks the row_number() over feature as well as the partition by.

Any idea?

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This is a pain in SQLite, because it doesn't support variables and it doesn't support window functions. Assuming you have no ties for a given name, you can do:

select t.name, sum(t.voters)
from t
where t.voters in (select t2.voters
                   from t t2
                   where t.name = t2.name
                   order by t2.voters desc
                   limit 2
                  )
group by t.name;

Upvotes: 1

Related Questions