•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 402,461 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,975 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 3350 | Replies: 1
 |
•
•
Join Date: Sep 2006
Posts: 1
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Computed column in view VS trigger on update 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!
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!
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 58
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) |
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- entry widget (Python)
- update database through gridview (ASP.NET)
- To update database through gridview (ASP.NET)
- Default value of a column (MS SQL)
- Good Luck w/This one: Excel Question VBA/Macro/other (MS Access and FileMaker Pro)
- Auto update columns? (Database Design)
Other Threads in the MS SQL Forum
- Previous Thread: Modal Function
- Next Thread: SQL query
