

Grasshopper
Group: General Forum Members
Last Login: Thursday, September 12, 2013 2:28 PM
Points: 12,
Visits: 159


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.




SSCommitted
Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 1,746,
Visits: 2,554


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) I'm not fat, I'm gravity challenged.




Grasshopper
Group: General Forum Members
Last Login: Thursday, September 12, 2013 2:28 PM
Points: 12,
Visits: 159


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



