Home Forums SQL Server 2008 T-SQL (SS2K8) How to reduce the number of table access from multiple "select count(*) group by" with union all RE: How to reduce the number of table access from multiple "select count(*) group by" with union all

  • Select

    A, B, C,

    sum(case when A between 1 and 100 and B between 1 and 100 and C between 1 and 100 then 1 else 0 end) AS '100',

    sum(case when A between 101 and 200 and B between 101 and 200 and C between 101 and 200 then 1 else 0 end) AS '200',

    sum(case when A between 201 and 300 and B between 201 and 300 and C between 201 and 300 then 1 else 0 end) AS '300',

    sum(case when A > 300 and B > 300 and C > 300 then 1 else 0 end) AS 'Over 300'

    from SameTable

    where

    (A between 1 and 100 and B between 1 and 100 and C between 1 and 100) or

    (A between 101 and 200 and B between 101 and 200 and C between 101 and 200) or

    (A between 201 and 300 and B between 201 and 300 and C between 201 and 300) or

    (A > 300 and B > 300 and C > 300)

    group by A,B,C

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".