February 22, 2013 at 11:31 am
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...
February 22, 2013 at 1:27 pm
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.
February 22, 2013 at 1:27 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 22, 2013 at 2:15 pm
That's great...
Let me test it out...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply