Mike Mike - 1 year ago 99
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.


This is what the relations look like:

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download