getting mathematical operation of tow result in sql

Question

I am using SQL Server I have 2 tables :

table1 : for buying data data {item_id,price ,...} table2 for shopingcart data (item_id,datetime,...)

How can I find the probability of buying which is equal to = number_of_item_in table2 / number_of_item_in table 1;

which is better :

use :
set count1=(select count(*) from table1)
set count2=(select count(*) from table2)
and print count2/count1;

or by something like :

select c2/c1 as probability from 
(
select count(*) as c1 from Table1 , select count(*) as c2 from table2) 

)

please which of them is more effective .

I think they should be almost the same. Did you compare the execution plans? — Yosi Dahari, Dec 06 '13 at 20:39

score 2 · Answer 1 · answered Dec 06 '13 at 20:40

If you look at the intention of the code, then they are essentially equivalent. The question is whether you want to use T-SQL with variables or just put everything in one statement. One reason to use variables is because you will re-use the values again.

Some notes on the syntax:

They will both return 0 or 1 because SQL Server does integer division.

The first is not correct syntactically. You should use:

declare @count1 float = (select count(*) from table1);
declare @count2 float = (select count(*) from table2);
print count2/count1;

Also, the second is not correct syntactically. Here is a correct version:

select c2/c1 as probability 
from (select cast(count(*) as float) as c1 from Table1) c1 cross join
     (select count(*) as c2 from table2) c2;

so which of them is much more effetcient – Maria Dec 06 '13 at 20:47 — Maria, Dec 06 '13 at 20:47

getting mathematical operation of tow result in sql

1 Answers1