Diff in Querys - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Diff in Querys

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-23 : 23:00:05

i have to 2 query can any one explain me what is the diff in

select subimsi,sum(Qty_usg),sum(totalUsage*groupsize) from subdaily ,bill_process where
subIMSI = imsi group by subimsi having sum(Qty_usg)<>sum(totalUsage*groupsize) order by subimsi

select imsi , bill_sum , daily_sum from
(select imsi , sum(Qty_usg) bill_sum from bill_process group by imsi) as t1 ,
(select subimsi , sum(totalUsage*groupsize) daily_sum from subdaily group by subimsi) as t2
where t1.imsi = t2.subimsi and bill_sum <> daily_sum

======================================
Ask to your self before u ask someone

aclarke
Posting Yak Master

133 Posts

Posted - 2002-04-24 : 00:19:27

It will take somebody better with SQL than me to figure this out without some DDL (and DML too if you're feeling particularly helpful). I tried but eventually gave up...

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-24 : 01:41:25

how about....

One was written by someone who used to work with Oracle. The other one was written by someone who had originally broken the query down into a series of steps...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-24 : 01:43:01

sorry - that was sarcasm - I just couldn't help it.

To me they both look identical, but I'd go with the first form. The optimizer may mean that execution time is identical, but I'd have to say (to my mind) that the first one makes more sense....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-24 : 01:46:22

oh yeah, and the first one has explicit ordering. Can't tell what the order will be on the second one.....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-24 : 01:51:44

actually I want to revise that to both were written by someone who learned Oracle or MySQL

what about
select subimsi,sum(Qty_usg),sum(totalUsage*groupsize)
from subdaily inner join bill_process on subIMSI = imsi
group by subimsi
having sum(Qty_usg)<>sum(totalUsage*groupsize)
order by subimsi

OK - I'll shut up now.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

aclarke
Posting Yak Master

133 Posts

Posted - 2002-04-24 : 01:52:14

quote:

To me they both look identical, but I'd go with the first form. The optimizer may mean that execution time is identical, but I'd have to say (to my mind) that the first one makes more sense....

Good, this makes me feel better about not being able to see any difference in the recordset returned...

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-24 : 02:05:41

WOAH BABY WAS I WRONG!!!!!

I just did some tests and it looks like
select imsi , bill_sum , daily_sum from
(select imsi , sum(Qty_usg) bill_sum from bill_process group by imsi) as t1 ,
(select subimsi , sum(totalUsage*groupsize) daily_sum from subdaily group by subimsi) as t2
where t1.imsi = t2.subimsi and bill_sum <> daily_sum

will be the quickest by far!

But you might also like to know, that doing an inner join appears to be about twice the speed of doing

from a,b where a.bla = b.bla

so you should probable go for

select imsi , bill_sum , daily_sum from
(select imsi , sum(Qty_usg) as bill_sum from bill_process group by imsi) t1 inner join
(select subimsi , sum(totalUsage*groupsize) as daily_sum from subdaily group by subimsi) t2
on t1.imsi = t2.subimsi and bill_sum <> daily_sum

They say you learn something new every day - guess I just did!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-24 : 22:48:46

thanks to all...

a little bit of oracel and bit of sql leads to big problem.. confused
i have 2 query which look a like but the result is diff cannot figure out why.. rrb has come out with the third one

query are

(1)
select subimsi,sum(Qty_usg),sum(totalUsage*groupsize) from subdaily ,bill_process where
subIMSI = imsi group by subimsi having sum(Qty_usg)<>sum(totalUsage*groupsize) order by subimsi

(2)
select imsi , bill_sum , daily_sum from
(select imsi , sum(Qty_usg) bill_sum from bill_process group by imsi) as t1 ,
(select subimsi , sum(totalUsage*groupsize) daily_sum from subdaily group by subimsi) as t2
where t1.imsi = t2.subimsi and bill_sum <> daily_sum order by subimsi

(3)
select imsi , bill_sum , daily_sum from
(select imsi , sum(Qty_usg) as bill_sum from bill_process group by imsi) t1 inner join
(select subimsi , sum(totalUsage*groupsize) as daily_sum from subdaily group by subimsi) t2
on t1.imsi = t2.subimsi and bill_sum <> daily_sum

the sample data look like this

table subdaily
subimsi totalusage groupsize
525127170000010 7.0000 4.00
525127170000010 7.0000 5.00

table bill_process
imsi Qty_usg
525127170000010 63

result by 3 query is
(1)
525127170000010 126.00 63.000000

(2)
no rows

(3)
no rows

now what is the problem and what is the diff can any of the sql guru help me out

======================================
Ask to your self before u ask someone

aclarke
Posting Yak Master

133 Posts

Posted - 2002-04-24 : 23:20:27

OK. To discuss why you aren't receiving anything from your second query:

Try running the following queries if you haven't already done so to determine what the "tables" (t1 & t2) contain that you're joining in:


select imsi , sum(Qty_usg) as bill_sum from bill_process group by imsi
select subimsi , sum(totalUsage*groupsize) as daily_sum from subdaily group by subimsi

You'll notice that they both return the same thing so that's why you're not getting any results there.

Now look at the first query. Perhaps changing it a bit to the following will help give you a picture of what's happening behind the scenes:


select sd.subimsi, bp.Qty_usg, (sd.totalUsage * sd.groupsize) as foo
from subdaily sd, bill_process bp
where sd.subIMSI = bp.imsi
order by sd.subimsi

I just added some aliases because I'm not as familiar with the tables. You'll get output like this:

subimsi, qty_usg, foo
525127170000010, 63, 28.0
525127170000010, 63, 35.0

Now remember that you're taking the sum of qty_usg and foo. So as 126 <> 63, you get a result back.

Hope this helps.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-24 : 23:34:27

thanks aclarke.... good at it

yea i got the point... when i was Analyzing i found something
the rows count in t1 and t2 are not same so when i using
t1.id=t2.id or inner join its actually retuning cartesian product
so when i sum it its result is diff ..

so using derived table the value are sum and grouped by id in t1 and t2 when i join the result look perfect..

as rrb said "someone who used to work with Oracle"

do we have any diff in writing in oracle or SQL

and when i see the execution plan how do we say which will be a better one.

======================================
Ask to your self before u ask someone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources