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 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... :)
ReplyDeleteI 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...
ReplyDeletehttp://mindstick.com/Articles/485e31f2-613e-4758-8d4f-5a5d74c3a2ed/?%E2%80%98SELECT%E2%80%99%20command%20with%20Aggregate%20Function
Thanks
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.
ReplyDelete-- 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?
SELECT R.BusinessType, AVG(
(SELECT
SUM(S2.SalesAmount) AS 'Total Sales of Reseller Sales'
FROM FactResellerSales AS S2
WHERE S2.ResellerKey = R.ResellerKey)) AS TotalSales,
R.ResellerKey
FROM DimReseller AS R
INNER JOIN FactResellerSales AS S
ON R.ResellerKey = S.ResellerKey
GROUP BY R.BusinessType, R.ResellerKey
ORDER BY SUM(S.SalesAmount) DESC