CODEWITHSUNDEEP
sqlaverage
Sam
Sam

Reputation: 143

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

Upvotes: 0

Views: 375

Answers (3)

ZevoFF
ZevoFF

Reputation: 1

Your solution is to use union all:

SELECT AVG(price) AS price
FROM (SELECT price,'pc' AS type
          FROM pc JOIN product ON product.model=pc.model WHERE maker ='a'
      UNION ALL
      SELECT price, 'laptop' AS type
          FROM laptop JOIN product ON product.model=laptop.model WHERE maker ='a')
   AS tow (price,type)

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Just to be clear, a simpler version looks like this:

select avg(price)
from pc
where model in (select model 
                from product
                 where maker='A');

Upvotes: 1

jpw
jpw

Reputation: 44871

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.

Upvotes: 3

Related Questions