CODEWITHSUNDEEP
sql
Benny
Benny

Reputation: 498

SQL - find average value by using two different tables

I have to find the average ram size of a pc (from pc_table) for all those manufacturers (from product_table) that making printers.

pc_table have these columns: model, speed, ram

product_table have these columns: model, manufacturer, typeOfProduct (pc/printer)

I've wrote this code:

SELECT a.manufacturer, AVG(b.ram) AS avg_ram_size
FROM pc_table b, (SELECT manufacturer, model
                  FROM product_table
                  WHERE type='PRINTER') a
WHERE b.model = a.model
GROUP BY a.manufacturer;

but for some reason, the output table is just empty.

Note: every manufacturer making both Printers & PCs, but still my output is empty table (which is wrong).

I just started with SQL, thanks for your help. (if any piece of info is missing, comment and i'll add it)

Upvotes: 0

Views: 722

Answers (1)

EdmCoff
EdmCoff

Reputation: 3576

It sounds like the pc_table only includes information for pc manufacturers, but you want information about pc manufacturers who also make printers. If that's the case, I suggest something like:

SELECT p.manufacturer, AVG(pc.ram) AS avg_ram_size
FROM product_table p INNER JOIN pc_table pc 
 ON pc.model = p.model
WHERE p.manufacturer IN
 (
  SELECT p2.manufacturer
  FROM product_table p2
  WHERE p2.type='PRINTER'
 )
GROUP BY p.manufacturer

Also your current join syntax is very old, the current ANSI syntax would use an INNER JOIN.

Upvotes: 2

Related Questions