SQLServerCentral » T-SQL (SS2K8) » How to reduce the number of table access from multiple "select count(*) group by" with union allInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralTue, 17 Jan 2017 06:42:20 GMT20How to reduce the number of table access from multiple "select count(*) group by" with union allhttps://www.sqlservercentral.com/Forums/FindPost1423185.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,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...
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 allhttps://www.sqlservercentral.com/Forums/FindPost1423264.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 allhttps://www.sqlservercentral.com/Forums/FindPost1423241.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 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
[/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 allhttps://www.sqlservercentral.com/Forums/FindPost1423240.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 GMThslawj