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?
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:It means that you cannot use
officetotal
andSUM(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)
orMAX(officetotal)
or some other approach.