SQL-ex.ru #26 Selecing average from two tables

Question

I had a question about a sql query on the website http://www.sql-ex.ru/. The query asks for :

Define the average price of the PCs and laptops produced by maker A.

The database schema is as follows:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

I wrote my query as:

SELECT AVG(t.k) AS Avg_Price
FROM
  (SELECT AVG(A.price) AS k
    FROM PC A 
  JOIN Product B ON(A.model=B.model)
  WHERE B.maker='A'
  UNION ALL
  SELECT AVG(C.price) AS k
     FROM Laptop C
  JOIN PRODUCT D ON(C.model=D.model)
  WHERE D.maker='A') AS t

The problem is that it does not return the correct answer. The average returned is much higher than expected. Is the way the average is calculated wrong? How do I change the query so that it returns the expected answer? Any help would be appreciated.

Thanks

Answer 1

You're averaging pc prices and laptop prices separately, then averaging the averages together. Your query was good except that you shouldn't have averaged the prices in the sub queries. simply return the prices in the sub queries and average at the top level:

select
    AVG( Price ) Avg_Price
from
(
    (
        select
            pc.Price
        from
            PC pc
            join Produt prod
             on pc.Model = prod.Model
        where
            prod.Maker = 'A'
    )
    union all
    (
        select
            pc.Price
        from
            Laptop l
            join Produt prod
             on l.Model = prod.Model
        where
            prod.Maker = 'A'
    )
) q

Answer 2

SELECT AVG(datatable.price) FROM
(
(SELECT PC.Price FROM PC INNER JOIN Product p1 ON PC.model=P1.model     
     WHERE P1.maker='A') 
UNION ALL 
(SELECT Laptop.price FROM Laptop INNER JOIN Product p2 ON 
    Laptop.model=P2.model WHERE P2.maker='A')
) datatable

Right.

The result of Your query:

Avg_Price

754.1666