Oliver S. Oliver S. - 4 months ago 6
SQL Question

Including SUM and COUNT without accessing table twice

I'm extracting

a
,
b
and
c
from
table_1
. Let's say,
a
represents a car model.
Now I'd like to add from
table_2
the sum people paid for this car and also the number of people who own this car.

Is there a more elegant way to do this than I did? I don't like the fact that I
access
table_2
twice and think that there must be an easier way.

I'd like to add that I work on an Oracle 11g database and the user I'm working with is not allowed to create views, procedures and the like.

select my_selection.*,
( select sum(amount)
from table_2 t2
where t2.a = t1.a ) as sum_buying_price,
( select count(*)
from table_2 t2
where t2.a = t1.a ) as count_car_owners
from ( select a,
b,
c
from table_1 t1
) my_selection


Thanks!

Answer

if a b c are unique, You can use this

select t1.a, t1.b, t1.c, sum(amount), count(*) from table_1 t1 left join table_2 t2 on t2.a = t1.a group by t1.a, t1.b, t1.c

pay attention to left join, as if there are no records, inner join wouldn't return this row.