Post reply

How to SUM

  • February 9, 2005 at 9:24 pm

    #65646

    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!

    Kindest Regards,

  • February 9, 2005 at 11:22 pm

    #541508

    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

    #541509

    Thank you.

    I feel emabarassed! You know when you have been coding all day and your brain starts to slow down late in the afternoon? I get that often!

    Kindest Regards,

  • February 10, 2005 at 2:34 am

    #541529

    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.

    LinkedIn Profile

  • February 10, 2005 at 3:52 pm

    #541697

    Thanks for that David and Mary. Both options work well.

    Kindest Regards,

  • February 11, 2005 at 6:39 am

    #541782

    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

    #543667

    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