Post reply

CASE Statement problem

  • April 26, 2012 at 7:44 am

    #273295

    Dear All,

    I'm trying to create a CASE statement which gives me a count of id that have a particular status and I'm trying to perform this by using the following statement:

    ......

    ......

    SUM(CASE WHEN Status = 'RG' THEN count(auID) END) AS CountRGStatus

    SUM(CASE WHEN Status = 'GS' THEN count(auID) END) AS CountGSStatus

    but I get the following error message:

    Msg 130, Level 15, State 1, Line 10

    Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    Are there other ways to achieve this please?

    Thank you in advance!

  • April 26, 2012 at 7:57 am

    #1479740

    SUM(CASE WHEN Status = 'RG' THEN 1 ELSE 0 END) AS CountRGStatus

    SUM(CASE WHEN Status = 'GS' THEN 1 ELSE 0 END) AS CountGSStatus

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • April 26, 2012 at 7:58 am

    #1479742

    Using a CTE may work for this if that's an option. Get the count in the CTE and the sum it in the main query. If you want further help having the full, relevant schema, sample data, and exact expected output would help.

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

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