April 21, 2011 at 8:22 am
Hi guys,
I have 2 queries and I feel its messy and could be optimized.
The thing is that when I tried, I got less rows and missing records.
Could you please give me a hand on this one?
Thanks,
-R
Here is the original query:
-------------------------------------
select
'bread' as type,
sp1,
cl1 as pr,
da,
d.m,
m.cu,
m.to,
sum(cast(init as float(10,2))) as ot,
count(*) as ev
from tbl1 d inner join tbl2 m on d.m = m.m
where non = 'M' and sp2 = 'Y'
group by sp1, cl1, da, d.m, m.cu, m.to
union
select
'sugar' as type,
sp1,
cl1 as pr,
da,
d.m,
m.cu,
m.to,
sum(case when sugar=1 then 1 else 0 end) as ot,
count(*) as ev
from tbl1 d inner join tbl2 m on d.m = m.m
where non = 'M' and sp2 = 'Y'
group by sp1, cl1, da, d.m, m.cu, m.to
------------------------------
April 21, 2011 at 8:29 am
answering this questions requires a bit more information. What is the point of the type of bread or sugar? Can that be gotten from a table? If the type can be gotten from a table how is it designated?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 21, 2011 at 8:32 am
Check the article linked in my footer and please provide DDL and sample data.
..and I agree with Dan.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 21, 2011 at 8:43 am
Not sure if this would be faster, depends on a lot of factors ie indexes, volume and other types or data not specified
SELECTt.[type],
sp1,
cl1 AS [pr],
da,
d.m,
m.cu,
m.[to],
SUM(CAST(CASE WHEN t.[type]='sugar' AND sugar=1 THEN 1.0
WHEN t.[type]='sugar' THEN 0.0
ELSE [init] END as float(10,2))) as [ot],
COUNT(*) as [ev]
FROMtbl1 d
JOIN tbl2 m ON d.m = m.m
CROSS JOIN (SELECT 'bread' AS [type] UNION ALL SELECT 'sugar') t
WHEREnon = 'M' AND sp2 = 'Y'
GROUP BY t.[type],sp1, cl1, da, d.m, m.cu, m.[to]
*EDITED to fix case statement
Far away is close at hand in the images of elsewhere.
Anon.
April 21, 2011 at 9:18 am
David,
it works! thank you very much!
Dan,
Im still studying this query as I'm new on this job and still getting a grip of things.
As soon as I get knowing this query I will be able to formulate better questions.
But thank you very much for taking the time and replying guys!
SQLSC all the way!
-R
April 21, 2011 at 9:30 am
Without being able to test I might suggest the following:
select
case when sugar=1 then 'sugar' else 'bread' end as type,
sp1,
cl1 as pr,
da,
d.m,
m.cu,
m.to,
sum(case when sugar=1 then 1 else cast(init as float(10,2)) end) as ot,
count(*) as ev
from tbl1 d inner join tbl2 m on d.m = m.m
where non = 'M' and sp2 = 'Y'
group by case when sugar=1 then 'sugar' else 'bread' end, sp1, cl1, da, d.m, m.cu, m.to
The probability of survival is inversely proportional to the angle of arrival.
April 21, 2011 at 10:17 am
Indexes are created already ?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 21, 2011 at 11:57 am
Sturner, your approach seems more like what I was expecting (without any UNION) but, however, it returns only half of the records for some reason.
Im still checking what it may be.
Thanks for trying to help!
-R
April 21, 2011 at 12:12 pm
Well, the query is aggregating rows based upon these unique entities:
case when sugar=1 then 'sugar' else 'bread' end, sp1, cl1, da, d.m, m.cu, m.to
So I am thinking that maybe the original query is in error since there is no difference in the where clauses of each select statement. What would be affected most is this: count(*) as ev because in the UNION case it would be counting rows twice, Vs. only once in the single query case.
Perhaps instead of simply using count(*) you need to use sum() with a case and have separate counts of sugar=1 and sugar <> 1
The probability of survival is inversely proportional to the angle of arrival.
April 21, 2011 at 12:17 pm
Thanks Mr,
I will try!
-R
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply