0

Hi guys,

Let me describe the situation I am in. I have a table of 2000 objects and each object has a range of different prices associated with it, stored in another table.

I have created a view which serves to list the objects as well as their average price. At present I have something along the lines of

CREATE VIEW objview AS
select o.*, (select sum(op.price)/count(*) from objprice op where op.objectid=o.objectid) averageprice
from obj o

I have a feeling that the above might not be very efficient.

Does anyone have any input regarding speed and efficiency on
- using a join in the above instead of a nested query
- having a fixed field in the original obj table which gets updated via a trigger when new values are inserted into objprice

Which is the best solution?

Thanks!

2
Contributors
1
Reply
2
Views
11 Years
Discussion Span
Last Post by hollystyles
0

Use SQL Servers AVG keyword/function

SELECT
    o.column1, o.column2, ...
    avg(p.price)
FROM
    Objecttable o
    JOIN Pricetable p on o.id = p.id
WHERE
    <put criteria if needed otherwise leave WHERE out>
GROUP BY 
    o.column1, 0.column2, .... (but not the column in the avg() expr)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.