February 9, 2005 at 9:24 pm
I have the following query,
SELECT COUNT(CASE WHEN Age BETWEEN '17' AND '29' THEN '1' END) AS '17-29',
COUNT(CASE WHEN Age BETWEEN '30' AND '39' THEN '1' END) AS '30-39',
COUNT(CASE WHEN Age BETWEEN '40' AND '49' THEN '1' END) AS '40-49',
COUNT(CASE WHEN Age >= '50' THEN '1' END) AS 'Over 50'
My question; Is there a way to sum the entire result? Example the 1st COUNT = 1, 2nd COUNT = 2, 3rd COUNT = 4, 4th COUNT = 5, can I return a column that will contain a figure of 12?
Sorry for asking such a basic question however, I have mental blank at the moment!
February 9, 2005 at 11:22 pm
Wont this solve the problem ?
SELECT COUNT(CASE WHEN id BETWEEN '17' AND '29' THEN '1' END) AS '17-29',
COUNT(CASE WHEN id BETWEEN '30' AND '39' THEN '1' END) AS '30-39',
COUNT(CASE WHEN id BETWEEN '40' AND '49' THEN '1' END) AS '40-49',
COUNT(CASE WHEN id >= '50' THEN '1' END) AS 'Over 50',
count(CASE WHEN id>=17 THEN '1' END)
from syscolumns
February 9, 2005 at 11:40 pm
February 10, 2005 at 2:34 am
If you are just interested in people of 17 or over then add WHERE age>=17 to your SQL, then a simple COUNT(*) will be the sum.
February 10, 2005 at 3:52 pm
February 11, 2005 at 6:39 am
Another option would be to wrap it all up as a subquery where the outer query does the summing. That'd be my first reaction.
However, this would no doubt be less efficent than the previous author's solution.
Cheers.
February 23, 2005 at 6:08 am
I think what you want is :
SELECT SUM(CASE WHEN id BETWEEN '17' AND '29' THEN '1' END) AS '17-29',
SUM(CASE WHEN id BETWEEN '30' AND '39' THEN '1' END) AS '30-39',
SUM(CASE WHEN id BETWEEN '40' AND '49' THEN '1' END) AS '40-49',
SUM(CASE WHEN id >= '50' THEN '1' END) AS 'Over 50',
SUM(CASE WHEN id>=17 THEN '1' END),
COUNT(*) AS AllAges
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply