Set Functions – COUNT, MAX, MIN, SUM and AVG
Many times the information we require from table doesn’t relate to individual rows but to set of rows. In such case we can use Set functions provided by the SQL Server. These function are COUNT, MAX, MIN, SUM and AVG. Set functions operate on groups of rows.
1. COUNT
The COUNT function returns the rows count in the table. When used with WHERE clause, only number of rows which satisfy the condition will be returned. The DISTINCT can be used to get the count of rows with unique values.
Example
USE AdventureWorks
– Calculates the row count of the table
SELECT COUNT(*) FROM Purchasing.PurchaseOrderDetail
– Calculates the row count which satisfy the condition
SELECT COUNT(*) FROM Purchasing.PurchaseOrderDetail WHERE PurchaseOrderID=18
– Calculates the number of non-null values for that column
SELECT COUNT(ProductID) FROM Purchasing.PurchaseOrderDetail
– Calculates the number of distinct non-null values for that column
SELECT COUNT(DISTINCT(ProductID)) FROM Purchasing.PurchaseOrderDetail
2. MAX
The MAX function returns the maximum value in the specified column. The MAX function can also be used as the part of sub-query.
Example
– Finds the maximum value for the column
SELECT MAX(OrderQty) FROM Purchasing.PurchaseOrderDetail
– It can be used as part of subquery
– Displays all the records having maximum OrderQty
SELECT * FROM Purchasing.PurchaseOrderDetail WHERE OrderQty =
(SELECT MAX(OrderQty) FROM Purchasing.PurchaseOrderDetail)
3. MIN
The MIN function returns the minimum value in the specified column. The MIN function can also be used as the part of sub-query.
Example
– Finds the minimum value for the column
SELECT MIN(OrderQty) FROM Purchasing.PurchaseOrderDetail
– It can be used as part of subquery
– Displays all the records having minimum OrderQty
SELECT * FROM Purchasing.PurchaseOrderDetail WHERE OrderQty =
(SELECT MIN(OrderQty) FROM Purchasing.PurchaseOrderDetail)
4. SUM
The SUM function adds up the values in a specified column. The column must be of one of numeric data types. The DISTINCT can be used to get the sum of only unique values.
Example
– Finds the total sum for the column
SELECT SUM(OrderQty) FROM Purchasing.PurchaseOrderDetail
– Finds the sum of distinct values for the column
SELECT SUM(DISTINCT(OrderQty)) FROM Purchasing.PurchaseOrderDetail
5. AVG
The AVG function returns the average of all the values in the specified column. As with SUM function, the column must be of one of numeric data types. The DISTINCT can be used to get the average of only unique values.
Example
– Finds the average for the column
SELECT AVG(OrderQty) FROM Purchasing.PurchaseOrderDetail
– Finds the average of distinct values for the column
SELECT AVG(DISTINCT(OrderQty)) FROM Purchasing.PurchaseOrderDetail
Good Luck!
