

Grasshopper
Group: General Forum Members
Last Login: Friday, October 16, 2015 12:44 PM
Points: 12,
Visits: 162


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




Forum Newbie
Group: General Forum Members
Last Login: Friday, July 12, 2013 7:21 AM
Points: 2,
Visits: 102


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.




Hall of Fame
Group: General Forum Members
Last Login: Friday, November 27, 2015 1:58 PM
Points: 3,277,
Visits: 5,101


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)
"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial




Grasshopper
Group: General Forum Members
Last Login: Friday, October 16, 2015 12:44 PM
Points: 12,
Visits: 162


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



