Column value divided by row count in SQL Server

Question

What happens when each column value in a table is divided with the total table row count. What function is basically performed by sql server? Can any one help?

More specifically: what is the difference between sum(column value ) / row count and column value/ row count. for e.g,

select cast(officetotal as float) /count(officeid) as value, 
sum(officetotal)/ count(officeid) as average from check1 
where officeid ='50009' group by officeid,officetotal

What is the operation performed on both select?

Answer 1

In your example both will be allways the same value because count(officeid) is allways equal to 1 because officeid is contained in the WHERE clause and officetotal is also contained in GROUP BY clause. So the example will not work because no grouping will be applied.

When you remove officetotal from the GROUP BY, you will get following message:

Column 'officetotal' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

It means that you cannot use officetotal and SUM(officetotal) in one select - because SUM is meant to work for set of values and it is pointless to SUM only one value.

It is just not possible to write it this way in SQL using GROUP BY. If you look for something like first or last value from a group, you will have to use MIN(officetotal) or MAX(officetotal) or some other approach.