CODEWITHSUNDEEP
sqlsubqueryaggregate
user
user

Reputation: 253

I need to find average price for two products in two separate tables where maker is the same for both

The problem: Find out the average price of PCs and laptops produced by maker A. Result set: one overall average price for all items.

Product table:

enter image description here

PC table:

enter image description here

Printer table:

enter image description here

Laptop table:

enter image description here

My code: 

select (avg(pc.price)+avg(printer.price))/2 from pc, printer
where pc.model IN
(SELECT product.model from PRODUCT where maker='A')
AND
printer.model IN
(SELECT printer.model from product where maker  = 'A')

My result and the correct result: enter image description here

Upvotes: 1

Views: 789

Answers (1)

forpas
forpas

Reputation: 164099

I don't think that the requirement is the average of 2 averages.
What you can do is select all prices of pcs and all prices of laptops by maker 'A' and find the average of all.
You will have to use UNION ALL to select all the prices from both tables pc and laptop:

select avg(t.price) AVG_price from (
  select price from pc
  where model in (select model from product where maker = 'A')
  union all
  select price from laptop
  where model in (select model from product where maker = 'A')
) t

But if it is the case of the average of averages, then by following the same logic, calculate the 2 averages and apply UNION ALL to get the average of them:

select avg(t.price) AVG_price from (
  select avg(price) price from pc
  where model in (select model from product where maker = 'A')
  union all
  select avg(price) price from laptop
  where model in (select model from product where maker = 'A')
) t

Upvotes: 1

Related Questions