Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2005-07-29 : 10:13:13
|
| BOL says the view cannot contain: The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functionsso can this view be a materialized view:create view my_stats asselect userClientID,count_big(salarybonuses) as Bonuses, avg(cast(tips as decimal)) as AvgTips from EmployeeEarningsgroup by userClientIDit is using AVG but I think there is some mention that when using with count_big and SUM would make it work? |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-07-30 : 22:03:05
|
quote:
Originally posted by sql777
BOL says the view cannot contain: The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functionsso can this view be a materialized view:create view my_stats asselect userClientID,count_big(salarybonuses) as Bonuses, avg(cast(tips as decimal)) as AvgTips from EmployeeEarningsgroup by userClientIDit is using AVG but I think there is some mention that when using with count_big and SUM would make it work?
You need to define the view to include SUM (tips) so that you can select AVG from the view. Here's what BOL says in SQL Server 2005 (also applies to 2000):The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG(expression) is specified in queries referencing the indexed view, the optimizer can frequently calculate the needed result if the view select list contains SUM(expression) and COUNT_BIG(expression). For example, an indexed view SELECT list cannot contain the expression AVG(column1). If the view SELECT list contains the expressions SUM(column1) and COUNT_BIG(column1), SQL Server can calculate the average for a query that references the view and specifies AVG(column1).Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
|
|
|
|
|
materialized views