CODEWITHSUNDEEP
sqljoinunion
Paul
Paul

Reputation: 1149

SQL-ex.ru #26 Selecing average from two tables

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

Upvotes: 2

Views: 4949

Answers (12)

Nishant
Nishant

Reputation: 1

select avg(P.price) from 
(select pc.model as model,pc.price as price from PC pc 
union all 
select L.model as model,L.price as price from Laptop L ) P
where P.model in 
( select model from Product where maker = 'A' )

Upvotes: 0

Kanish
Kanish

Reputation: 321

I always have great struggle with JOINS(personally), so try this solution without joins. using IN Operator

with cte as (
    SELECT price, model, code 
    FROM pc 
    WHERE model 
    IN (SELECT model FROM product WHERE maker = 'A')
UNION
    SELECT price, model, code 
    FROM laptop 
    WHERE model 
    IN (SELECT model FROM product WHERE maker = 'A')
)

SELECT AVG(price) FROM cte

Upvotes: 0

uttam rawat
uttam rawat

Reputation: 9

It also work

SELECT Avg(price) AS avg_price
FROM   (SELECT pc.price
        FROM   pc
               INNER JOIN product
                       ON pc.model = product.model
        WHERE  maker = 'A'
        UNION ALL
        SELECT laptop.price
        FROM   laptop
               INNER JOIN product
                       ON laptop.model = product.model
        WHERE  maker = 'A') Any_name

Upvotes: 1

srinath Thapa
srinath Thapa

Reputation: 1

select avg(price) as avg_price from
(select price
from Product, PC
where Product.model = PC.model
and maker = 'A'
union all
select price
from Product, Laptop
where Product.model = Laptop.model
and maker='A') avg_price

Upvotes: -1

user11109122
user11109122

Reputation: 9

Select avg(price) from
       ((select price,model  from pc 
       union all
       select price,model from laptop) a inner join  product on product.model=a.model and product.maker='A' )

Firstly, I think that it would be better if I union price and model of both tables and afterwards we can find average(price) from maker A.

Upvotes: 0

MxKap
MxKap

Reputation: 28

SELECT AVG(price) FROM 
(
     SELECT PC.price, Product.maker FROM PC JOIN Product ON(PC.model=Product.model)
     UNION ALL
     SELECT Laptop.price, Product.maker FROM Laptop JOIN Product ON (Laptop.model = Product.model)
) T 
GROUP BY T.maker HAVING T.maker = 'A'

It works because at first you select all laptop and pc prices, next you choose only records where maker = 'A', and the last step is counting an avg price.

Upvotes: 0

Maverick Meerkat
Maverick Meerkat

Reputation: 6414

with t1 as
(SELECT price, type from pc left join product on product.model = pc.model where maker = 'A'
UNION ALL
SELECT price, type from laptop left join product on product.model = laptop.model where maker = 'A')
select avg(price) from t1

Upvotes: 1

learning
learning

Reputation: 73

SELECT AVG(datatable.price) FROM
(
(SELECT PC.Price FROM PC INNER JOIN Product p1 ON PC.model=P1.model     
     WHERE P1.maker='A') 
UNION ALL 
(SELECT Laptop.price FROM Laptop INNER JOIN Product p2 ON 
    Laptop.model=P2.model WHERE P2.maker='A')
) datatable

Right.

The result of Your query:

Avg_Price

754.1666

Upvotes: 3

Eugene Ovdiyuk
Eugene Ovdiyuk

Reputation: 1

select AVG(P.price) as avg_price
from
(
 select price, model from PC
 UNION ALL
 select price, model from Laptop
) P
join Product ON P.model = Product.model
where maker = 'A'

Upvotes: 0

John Woo
John Woo

Reputation: 263803

SELECT AVG(resultList.Price)
FROM
    (
        SELECT  a.Price
        FROM    PC a INNER JOIN Product B 
                    on a.Model = B.Model
        WHERE   B.Maker = 'A'
        UNION ALL
        SELECT  c.Price
        FROM    Laptop c INNER JOIN Product d 
                    on c.Model = d.Model
        WHERE   d.Maker = 'A'
    ) resultList

Upvotes: 0

Moho
Moho

Reputation: 16543

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

Upvotes: 4

user2214384
user2214384

Reputation:

Your query is wrong. It is typically like how you to aggregation on distributed system. You can do aggregation on distributed node, then bring back the aggregation data and do agg on top of you return data, only except for the aggregation is transferable. AVG is not transerable. (1+2+3+4+5)/5 <> ((1+2)/2 + (1+2+3)/3)

Upvotes: 2

Related Questions