Post reply

How to reduce the number of table access from multiple "select count(*) group by" with union all

  • February 22, 2013 at 11:31 am

    #268966

    I have a select statement as below, which causes a table access for each union all..

    My goal is to cut down the number of table access without using union all..

    Select A, B, C, '100' as CntType, count(*) as Cnt from SameTable where A between 1 and 100 and B between 1 and 100 and C between 1 and 100 group by A,B,C

    Union all

    Select A, B, C, '200' as CntType, count(*) as Cnt from SameTable where A between 101 and 200 and B between 101 and 200 and C between 101 and 200 group by A,B,C

    Union all

    Select A, B, C, '300' as CntType, count(*) as Cnt from SameTable where A between 201 and 300 and B between 201 and 300 and C between 201 and 300 group by A,B,C

    Union all

    Select A, B, C, 'Over 300' as CntType, count(*) as Cnt from SameTable where A > 300 and B > 300 and C > 300 group by A,B,C

    The above Union all will access the SameTable four times, I'd like to have SQL statements to access the SameTable once..

    Please HELP!

    Happy Friday...

  • February 22, 2013 at 1:27 pm

    #1590511

    This may be way off base but could you add a column to your query and use CASE to populate it based on your A, B & C values? Then you could include that in your GROUP BY.

  • 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.

  • February 22, 2013 at 2:15 pm

    #1590531

    That's great...

    Let me test it out...

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

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