CODEWITHSUNDEEP
sql
theblindprophet
theblindprophet

Reputation: 7927

Average of rows with different column values

2 tables

model is a foreign key between Product and PC

I need to select all rows in Product that have the same maker and average their price.

Therefore with these tables:

Product       PC
-------       -------
A    1        1    60
A    2        2    80
B    3        3    110
B    4        4    140

I should get:

maker      avg(price)
---------------------
A          70
B          125

I have tried this, but have no idea how to combine the rows and average just those that get combined. This displays the average across the entire PC table for all maker.

select 
    Product.maker, Product.model, avg(PC.price) 
from 
    Product, PC 
group by 
    Product.maker, Product.model 
order by 
    maker asc;

Upvotes: 0

Views: 35

Answers (1)

juergen d
juergen d

Reputation: 204746

Don't use the old legacy implicit join syntax any more. Use explicit joins

select Product.maker, avg(PC.price) 
from Product
left join PC on PC.model = Product.model
group by Product.maker
order by Product.maker asc

Upvotes: 2

Related Questions