Post reply

Group by and Where filters

  • July 11, 2013 at 10:25 am

    #296238

    I have a query that seems to operate differently than I though SQL handled Group By with Where...

    I thought WHERE is used to filter the rows then the group by is done.. but what I am seeing is that the count, and SUM sections have totals for all the rows.. prior to filter.

    Is this correct? I thought that was how the HAVING worked, but the WHERE was before the totals were generated.

  • July 11, 2013 at 10:35 am

    #1631680

    dwilliscp (7/11/2013)

    I thought WHERE is used to filter the rows then the group by is done..

    That is indeed how it works.

    but what I am seeing is that the count, and SUM sections have totals for all the rows.. prior to filter.

    Query and sample data to show the problem please?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 11, 2013 at 11:04 am

    #1631691

    I just noticed what the developer did.. the long where statement had the following..

    ... and (mcode = mcode or mcode = '(ALL)') .. and

    it should have been

    ...and (mcode = @mcode or mcode = '(ALL)') .. and

    I looked over that code for 30min without seeing that... my mind was correcting what I was seeing.

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

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