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.
thanks 4 posting… 🙂
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