performance with data link

[raji96]

I have a query which is in a loop and is like this

select sum(a.time),sum(a.cost)
from a,b
where b.rid=10
and a.id=2
and a.id=b.id

The problem is we are connecting to the database via data link and is talking time to get connected,for eg 2 sec to connect and the loop can go only for 10 parts, so 20secs for connection alone.
So we are thinking of in clause instead of loop, but not sure how to sum it, since the data is like this

rid id time cost
10 2 20 200
10 5 20 100
9 6 100 100
10 2 50 400
10 5 10 50
10 5 10 100

My result should look like for a.id=2
70 600
My result should look like for a.id=5
40 250
My result should look like for a.id=6
100 100

When i use 'in'(a.id in(2,5,6) clause in the same query the result is
210, 950

I can understand what the query is doing but not sure how to get the desire result.

Please help !
Thanks!

 

[carp]

If I understand what you are trying to do, see if this helps:

select a.id, sum(a.time),sum(a.cost)
from a,b
where b.rid=10
and a.id IN (2,5,6)
and a.id=b.id
GROUP BY a.id;

Elbert, CO
1747 MST