Post reply

SELECT 'HAVING' issue

  • March 31, 2008 at 1:08 pm

    #91804

    This has got to be one of the dumber questions I've asked but I have a @tmpTable that I'm trying to get a count of records for, grouped by a column where the sum of said column = 7

    data:

    CheckType,Code

    6,XYZZY

    1,ABCDEF

    1,00209TAA3

    1,437076AR3

    1,456866AS1

    1,68608JHC8

    6,00209TAA3

    6,437076AR3

    6,456866AS1

    6,68608JHC8

    So I'm trying to do

    SELECT @intCountViolation=count(*)

    from @tmpTable

    GROUP BY CODE

    HAVING SUM(CheckType)=7

    The XYZZY,ABCDEF records are the only unique ones... I'm expecting to get a count of 4 but I am getting a count of 2. I can even leave off the XYZZY,ABDEF records and I still get 2.

    Am I missing something obvious?

    thanks,

    Chris

  • March 31, 2008 at 1:23 pm

    #796137

    If you take the assignment off - you'll see that you're getting the counts within each group that adds up to 7, not the count of the groups. Meaning - it's 2 because 1 and 6 (two elements) went into making the 7. If it had been 1,2,4, then you'd have seen 3.

    You're looking for something like this:

    select count(*)

    from (select code

    from #mytable

    GROUP BY CODE

    HAVING SUM(CheckType)=7) grped

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • March 31, 2008 at 1:42 pm

    #796144

    *smacks head*

    thanks 🙂

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

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