Mike Mike - 2 months ago 20
SQL Question

MySQL query to return AVG

Just a practice problem, little bit confusing I'd appreciate it if somebody could clear this up..

I have a DB called Product and another one called PC. I'm trying to execute a query to "find the average hard disk size of a PC for all those manufacturers who also make printers"

Here's my code:

SELECT maker, AVG(hd)
FROM pc, product
WHERE pc.model=product.model
GROUP BY maker
HAVING COUNT(type='PR')>=1


type is an attribute of Product which either stands for printer(PR) laptop or PC, however the HAVING statement doesn't seem to single out only the makers who produce printers, instead I get back an AVG for each maker in the Product table.

UPDATE

This is what the relations look like:

Product(maker, model, type)
PC(model, hd, speed, ram, price)
Printer(model, color, type, price)

Answer

Try the following query:

SELECT maker, AVG(hd) FROM PC, Product
WHERE PC.model=Product.model
AND Product.maker IN 
   (SELECT DISTINCT maker FROM Product WHERE type='PR')
GROUP BY Product.maker;

Demo: http://sqlfiddle.com/#!2/abfaa/2

You simply add a condition to make sure that the maker is one of the makers that have at least one printer product. You then group by the maker to find the individual averages.

Comments