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