Getting wrong average in a SQL query

Question

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 · Accepted Answer · 2016-06-14 19:37:32Z

3

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.

answered Jun 14, 2016 at 19:37

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Over a year ago

That was it. Thank you so much

Gordon Linoff · Accepted Answer · 2016-06-14 21:54:05Z

1

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');

answered Jun 14, 2016 at 21:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Toby Speight · Accepted Answer · 2018-06-26 10:39:18Z

-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)

edited Jun 26, 2018 at 10:39

Toby Speight

32.4k58 gold badges83 silver badges118 bronze badges

answered Jun 26, 2018 at 10:12

ZevoFF

1

Collectives™ on Stack Overflow

Getting wrong average in a SQL query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related