Frustrating nulls with average

I have a field of values of which any can be null. I can deal with these pretty easily in all cases but the average function. I need to be able to take an average that ignores null values.
So for example if I have 5, 7, null, 5 , 7 I want the average to be 6 not 4.8.You need to count the records where {table.that_field}<>''.

Create a running total, let's say 'count_not_null':

Select field to summarize;
Type of summaries: 'count'.
Evaluate: use a formula: {table.that_field}<>''
Reset it if you need.

Then create a formula Average:

sum({table.field})/{#count_not_null}|||Thanks. I figured it would be something like that...but I just couldn't figure out the angle of attack. Works great.|||Can anyone offer a solution for the same problem except when trying to calculate median?

Fragmented System Tables

I have found that a bunch of our tables were highly fragemented. (in some cases as much as 80%)
I used DBCC dBReindex to correct all the user tables, but how do I defrag the system table indexes?
Thanks--all except clustered indexes on sysobjects and sysindexes
dbcc dbrepair(@.dbname, repairindex, @.tabname, @.indid)

-- And an example
exec sp_dboption 'YourDatabase','single user','true'
dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'

-- And better encapsulated in undocumented MS SP
exec sp_dboption 'YourDatabase','single user','true'
exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'|||Originally posted by ispaleny
--all except clustered indexes on sysobjects and sysindexes
dbcc dbrepair(@.dbname, repairindex, @.tabname, @.indid)

-- And an example
exec sp_dboption 'YourDatabase','single user','true'
dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'

-- And better encapsulated in undocumented MS SP
exec sp_dboption 'YourDatabase','single user','true'
exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'

Thank you very much,

2 additional related questions

1) Can this be set to run weekly via a scheduled Job?
2) Can this be done with users still connected

(we have users connected 24/7 via internet)

Thanks again.|||Q1) Can this be set to run weekly via a scheduled Job?
A1) Yes. But you must add a script for disconnecting users.

Q2) Can this be done with users still connected
A2) No. The database must be in a single user mode.

I must be a funny job to maintain a living database :)
Let me know how you solved it.

Good luck !|||If you have set up your web site to have one place where the connections are made AND if the database is not THAT huge, you could replicate it, run the corrections, point the connections to the "new" database, replace the old one and go on.

That should be something that could certainly be automated. Might be more work than its worth tho...