Post reply

getting single line from case statment

  • January 21, 2011 at 6:11 am

    #104029

    I have a temp table and I am querying with the following

    SELECT LOC_NUM, ISNULL(CASE DEPARTMENT WHEN 'CCD' THEN COUNT(*) END,0) AS CCD,

    ISNULL(CASE DEPARTMENT WHEN 'BRANCH' THEN COUNT(*) END,0) AS BRANCH

    here are my resualts

    LOC NUM CCD BRANCH

    10 0 228

    10 215 0

    How can I get this query to show all on one line? I know I can put it into another Temp table, and sum it from there, but I wanted to see if there was a way to do it with this query, or with one query.

    ie... I would like my resaults to be:

    LOC NUM CCD BRANCH

    10 215 228

    Thanks much!

  • January 21, 2011 at 6:21 am

    #1276500

    Try THis one....

    SELECT LOC_NUM, MAX(ISNULL(CASE DEPARTMENT WHEN 'CCD' THEN COUNT(*) END,0)) AS CCD,

    MAX(ISNULL(CASE DEPARTMENT WHEN 'BRANCH' THEN COUNT(*) END,0)) AS BRANCH

    GROUP BY LOC_NUM

    Abhijit - http://abhijitmore.wordpress.com

  • January 21, 2011 at 6:26 am

    #1276503

    Hmm...

    i'm getting a

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

    errror.

  • January 21, 2011 at 6:40 am

    #1276510

    Something like this ?

    SELECT LOC_NUM, sum(case DEPARTMENT WHEN 'CCD' THEN 1 else 0 end) AS CCD,

    sum(case DEPARTMENT WHEN 'BRANCH' THEN 1 else 0 end) AS BRANCH

    from <Yourtable>

    group by LOC_NUM


    Clear Sky SQL
    My Blog[/url]

  • January 21, 2011 at 6:44 am

    #1276512

    That worked. Thanks so much Dave.

Viewing 5 posts - 1 through 4 (of 4 total)

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