Re: SQL Question

I gotcha,

Here U go :

select t.sum,e.sum from
(select ssn,sum(hours) "sum" from comptime_earned group by ssn) e, (select ssn,sum(hours) "sum" from comptime)_taken group by ssn ) t where t.ssn=e.ssn;

Thats it!

Mohamed Buhari
ORACLE DBA Buck Turgidson wrote:

> Screwed up big-time today. I joined 2 tables as such, and got a wrong
> answer:
>
> select
> sum(e.hours)
> ,sum(t.hours)
> from comptime_earned e,
> comptime_taken t
> where e.ssn = t.ssn (+)
>
> As an example, one person had 1 row in earned of 8 hours, and taken had 3
> rows of 2 hours each. When it summed, it tripled the 8 hours, resulting in
> over-paying 18 hours instead of 2. Ouch. I guess it did the sum function
> in a nested loops fashion for each of the 3 rows on the joined table.
>
> Is there a way to prevent this? I was thinking about using an in-line view
> for the summation of the 2 tables. Is there a better way?
Received on Mon Jan 24 2000 - 19:43:09 CST