## Tuesday, August 02, 2011

### What are the aggregate functions in SQL server?

Count, Min, Max and Average are the few aggregate functions in SQL Server.
Count function:  Select count (*) from Table1.  It will deliver count of the table i.e. no of records. If you will put the filters in the query then it will give the count of filtered records.
table Employee as designed below.

 Name Age Salary Total No of CARS Randheer 19 10000 1 Nids 17 50000 2 Jayant null 3 Ruby 5 2000 0

Case 1
Question.  Select  Count (Salary) from Employee?
A)    4  B)0 C)3 D) 2
Answer: In this case answer will be C as the null values in a column doesn’t counts by the count function in SQL Server.
Case 2
Question.  Select  Count (Age) from Employee?
A)    4  B)0 C)3 D) 2
Answer: In this case answer will be A as the space will be consider as a value by count function in SQL Server.

Min function: Min function retrieves the minimum values in a column it works on group by function and also without any group by function.

select min(salary) from(select 1 as ID, 'Randheer' as Name,'10000' as salaryunion allselect 1 as ID, 'Randheer' as Name,null as salaryunion allselect 1 as ID, 'Randheer' as Name,'80000' as salary)

Result will 10000 in  this case. it shows that min function is not considering the null value.
Max Function: Max function is used to get the max value of a column.
A
Select Max (column1) from table1
Average Function: Average is used to get the average of the column. Average can be on the groups also. This functions returns value as mathematics average function returns.

1. thanks 4 posting... :)

2. I have found another nice post over the internet which is also having a wonderful explanation on sql server aggregate function or sql function. For more details of that post please check out this link...
http://mindstick.com/Articles/485e31f2-613e-4758-8d4f-5a5d74c3a2ed/?%E2%80%98SELECT%E2%80%99%20command%20with%20Aggregate%20Function

Thanks

3. How about an aggregate function within an aggregate function? For example, -- List the amount of the average the total sales of reseller sales by business type.
-- To do this find the total sales for each reseller first and then find the average
-- of the total of all sales by a reseller within a business type. So far, I wrote the following code but it gives back error messages. Any ideas?