MySQL query to return AVG

Question

Just a practical problem, a bit confusing, I would appreciate it if anyone could clarify this.

I have a DB called Product and the other is PC. I am trying to fulfill the query "find the average size of the PC hard drive for all those manufacturers who also make printers"

Here is my code:

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

type is the Product attribute, which is designed for either a laptop computer (PR) or a PC, however, only manufacturers who produce printers are not shown in the HAVING statement, instead I return the AVG for each manufacturer to the Product Table.

UPDATE

Here's what the relationship looks like:

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

Answer 1

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 manufacturer is one of the manufacturers that has at least one printer. Then you create a group to find individual averages.

Answer 2

All the manufacturers that make printers:

  SELECT maker FROM product WHERE type = 'PR' GROUP BY maker; 

The average size of a PC hard drive for all those manufacturers that also make printers

  SELECT p.maker, AVG(pc.hd) avg_hd_size FROM ( SELECT maker FROM product WHERE type = 'PR' GROUP BY maker ) g JOIN product p on p.maker = g.maker JOIN pc on pc.model = p.model GROUP BY p.maker; 

Answer 3

---

SELECT manufacturer, avg (hd)

FROM INNER JOIN PC Product

ON PC.model = Product.model

GROUP BY manufacturer

HAVING manufacturer IN ( SELECTING DISTINCT manufacturer FROM Product WHERE type = 'Printer')

Answer 4

 select distinct maker ,avg(hd) as Avg_hd from Product inner join PC on (Product.model = PC.model) where type = 'Pc' and maker in (Select maker from Product where type = 'Printer') Group by maker