SQL Server – Set Functions – COUNT, MAX, MIN, SUM and AVG

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!

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: