Top N sales with total for remaining countries
I have countries and sales for those countries. I'd like the TOP N countries listed on their own and then a total for the rest of the countries. The sales must be for the year 2005 only.
We'll quickly set up the table and populate it with some sample data.
create table newsales(
country varchar(15),
sales int,
year int);
insert into newsales values
('canada', 55 , 2005 ),
('england', 40 , 2005 ),
('spain', 32 , 2005 ),
('germany', 18 , 2005 ),
('germany', 29 , 2005 ),
('morocco', 64 , 2005 ),
('ireland', 18 , 2005 ),
('ireland', 80 , 2003 ),
('portugal', 10 , 2005 ),
('portugal', 20 , 2006 );
In the following sum(case when year=2005 then sales else 0 end) says to do the following: give the sum of the sales when year =2005. if the year is not 2005 then add 0. This is necessary for the subqueries later on so I will introduce it in the first query so as not to confuse later on.
this will give you a list of all countries, along with the sums for each country
select
country,
sum(case when year=2005 then sales else 0 end) as total
from newsales
group by country
order by total desc
this will give you the list of countries with a total below those of the top three countries (for the year 2005 records only of course). the limit says give us the one row after the number of countries listed (in this case 2), so that is the third record. It now outputs all countries with sales lower than that number.
select country
from newsales
group by country
having sum(case when year=2005 then sales else 0 end)
<
(select
sum(case when year=2005 then sales else 0 end) as total
from newsales
group by country
order by
total desc
limit 2,1)
So this will give you the total for those countries outside of the top two.
select sum(case when year=2005 then sales else 0 end) as total
from newsales
where country in
(select country
from newsales
group by country
having sum(case when year=2005 then sales else 0 end) <
(select sum(case when year=2005 then sales else 0 end) as total
from newsales
group by country
order by total desc
limit 2,1))
Here is a slight change to it that will help us with a step below (note you can always select text and give it an alias, this is needed so we have a heading for the rest of the world total in the union below. without it it will show up as NULL) :
select
'rest of world' as country,
sum(case when year=2005 then sales else 0 end) as total
from newsales
where country in
(select country
from newsales
group by country
having sum(case when year=2005 then sales else 0 end) <
(select sum(case when year=2005 then sales else 0 end) as total
from newsales
group by country
order by total desc
limit 2,1))
You can now alter the top query to give you only three countries easily enough.
select
country,
sum(case when year=2005 then sales else 0 end) as total
from newsales
group by country
order by total desc
limit 3
which you now do a union with query four.
(
select
country,
sum(case when year=2005 then sales else 0 end) as total
from newsales
group by country
order by total desc
limit 3
)
union
(select
'rest of world' as country,
sum(case when year=2005 then sales else 0 end) as total
from newsales
where country in
(select country
from newsales
group by country
having sum(case when year=2005 then sales else 0 end) <
(select sum(case when year=2005 then sales else 0 end) as total
from newsales
group by country
order by total desc
limit 2,1))
)