i have two tables that share a field with items. i can count the
occurences for each table like "select count(item), item from items1
group by item" and the same for items2. now i want to compute the
ratio between the item count for each item from items1and items2. is
this possible in one query? if not, how would you suggest i approach
this problem? any pointers much appreciated,

andrej

Are you using stored procedures?
One approach could be:
declare 2 variables , gateher the COUNT information and then calculate ratio
Another approach is to do JOIN on the tables and calculate the ratio on the
fly

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




<omeldoid@gmail.com> wrote in message
news:1192191998.860353.68470@t8g2000prg.googlegrou ps.com...
>i have two tables that share a field with items. i can count the
> occurences for each table like "select count(item), item from items1
> group by item" and the same for items2. now i want to compute the
> ratio between the item count for each item from items1and items2. is
> this possible in one query? if not, how would you suggest i approach
> this problem? any pointers much appreciated,
>
> andrej
>

On 12 Oct, 13:26, omeld...@gmail.com wrote:
> i have two tables that share a field with items. i can count the
> occurences for each table like "select count(item), item from items1
> group by item" and the same for items2. now i want to compute the
> ratio between the item count for each item from items1and items2. is
> this possible in one query? if not, how would you suggest i approach
> this problem? any pointers much appreciated,
>
> andrej

Assuming that you have some sort of primary key for these tables
(which I have called id)

SELECT
i1.item,
count( DISTINCT i1.item, i1.id ) / count( DISTINCT i2.item, i2.id )
FROM items1 i1
JOIN items2 i2 USING ( item )
GROUP BY i1.item

On Oct 15, 1:27 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> SELECT
> i1.item,
> count( DISTINCT i1.item, i1.id ) / count( DISTINCT i2.item, i2.id )
> FROM items1 i1
> JOIN items2 i2 USING ( item )
> GROUP BY i1.item

thank you very much! based on your suggestion i've found the following
to do what i want (the trick is to count the dates):

select
t1.item,
count(distinct t1.date) / count(distinct t2.date)
from
items1 as t1
join
items2 as t2 using(item)
group by t1.item;

however, it is rather slow.