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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » How to reduce the number of table access from...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Jay Byoun

Posted Friday, February 22, 2013 11:31 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:03 PM
Points: 10, Visits: 138

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

Post #1423185

hslawj

Posted Friday, February 22, 2013 1:27 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 11:54 AM
Points: 2, Visits: 95

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.

Post #1423240

ScottPletcher

Posted Friday, February 22, 2013 1:27 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 1,318, Visits: 1,763

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)
One man with courage makes a majority. Andrew Jackson

Post #1423241

Jay Byoun

Posted Friday, February 22, 2013 2:15 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:03 PM
Points: 10, Visits: 138

That's great...
Let me test it out...

Post #1423264

« Prev Topic | Next Topic »

Permissions