Post reply

using count(*) and case in on statement

  • July 9, 2014 at 10:37 am

    #296291

    Hi,

    I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.

    Right now I am achieving this by the following case statement

    SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class'

    WHEN Stcount>=10 and StCount<=50THEN 'Medium Class'

    WHEN Stcount>50 THEN 'Big Class'

    END

    FROM(

    select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl

    But can I do this with just one select statement?

    Thanks.

  • July 9, 2014 at 10:56 am

    #1728151

    That's one select statement 🙂

    Do you mean something like this?

    SELECT 'ClassSize' = CASE

    WHEN COUNT(studentid) < 10

    THEN 'Small Class'

    WHEN COUNT(studentid) >= 10

    AND COUNT(studentid) <= 50

    THEN 'Medium Class'

    WHEN COUNT(studentid) > 50

    THEN 'Big Class'

    END

    FROM dbo.student

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 9, 2014 at 11:20 am

    #1728163

    Thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply