Samuel del Rio Samuel del Rio - 1 year ago 165
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
(select price
from pc
where model in (
select model
from product
where maker='A'
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: Exercise 26

Any help would be highly appreciated

jpw jpw
Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download