Samuel del Rio Samuel del Rio - 5 months ago 76
SQL Question

Getting wrong average in a SQL query


Question: Find out the average price of PCs and laptops produced by maker A.

Result set: one overall average price for all items.

Database Schema:
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 have the following code:

with totalproducts
as
(select price
from pc
where model in (
select model
from product
where maker='A'
)
union
select price
from laptop
where model in (
select model
from product
where maker='A'
))
select avg(price) from totalproducts


However, I get an average of 794.4444 and solution is 754.1666

in: http://www.sql-ex.ru/learn_exercises.php Exercise 26

Any help would be highly appreciated

jpw jpw
Answer

There might be better solutions, but to address the problem in your query:

Using union without the all qualifier removes duplicates, and presumably there might be a pc and a laptop with the same price. Change it to union all to keep duplicates from both sets and you'll probably get the expected result.