Summing the results of Case queries in SQL
- by David Stelfox
I think this is a relatively straightforward question but I have spent the afternoon looking for an answer and cannot yet find it. So...
I have a view with a country column and a number column. I want to make any number less than 10 'other' and then sum the 'other's into one value.
For example,
AR 10
AT 7
AU 11
BB 2
BE 23
BY 1
CL 2
I used CASE as follows:
select country = case
when number < 10 then 'Other'
else country
end,
number
from ...
This replaces the countries values with less than 10 in the number column to other but I can't work out how to sum them. I want to end up with a table/view which looks like this:
AR 10
AU 11
BE 23
Other 12
Any help is greatly appreciated.
Cheers,
David
