SQLServerCentral.com / SQL Server 2008 / T-SQL (SS2K8) / How to reduce the number of table access from multiple "select count(*) group by" with union all / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 17 Sep 2014 05:53:05 GMT20RE: How to reduce the number of table access from multiple "select count(*) group by" with union allhttp://www.sqlservercentral.com/Forums/Topic1423185-392-1.aspxThat's great...Let me test it out...Fri, 22 Feb 2013 14:15:15 GMTJay ByounRE: How to reduce the number of table access from multiple "select count(*) group by" with union allhttp://www.sqlservercentral.com/Forums/Topic1423185-392-1.aspx[code="sql"]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 SameTablewhere (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[/code]Fri, 22 Feb 2013 13:27:46 GMTScottPletcherRE: How to reduce the number of table access from multiple "select count(*) group by" with union allhttp://www.sqlservercentral.com/Forums/Topic1423185-392-1.aspxThis 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.Fri, 22 Feb 2013 13:27:17 GMThslawjHow to reduce the number of table access from multiple "select count(*) group by" with union allhttp://www.sqlservercentral.com/Forums/Topic1423185-392-1.aspxI 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,CUnion allSelect 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,CUnion allSelect 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,CUnion allSelect 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,CThe 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...Fri, 22 Feb 2013 11:31:02 GMTJay Byoun