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

  • February 22, 2013 at 1:27 pm

    #1590512

    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) A socialist is someone who will give you the shirt off *someone else's* back.