# Aggregate Functions

## Introduction

Aggregate functions in SQL Server run calculations on sets of values, returning a single value.

## Syntax

• AVG([ALL|DISTINCT]expression)
• COUNT([ALL|DISTINCT]expression)
• MAX([ALL|DISTINCT]expression)
• MIN([ALL|DISTINCT]expression)
• SUM([ALL|DISTINCT]expression)

## AVG()

Returns average of numeric values in a given column.

We have table as shown in figure that will be used to perform different aggregate functions. The table name is Marksheet. ``````Select AVG(MarksObtained) From Marksheet
``````

The `average` function doesn't consider rows with NULL value in the field used as parameter

In the above example if we have another row like this:

``````106    Italian    NULL
``````

This row will not be consider in average calculation

## COUNT()

Returns the total number of values in a given column.

We have table as shown in figure that will be used to perform different aggregate functions. The table name is Marksheet. ``````Select COUNT(MarksObtained) From Marksheet
``````

The `count` function doesn't consider rows with NULL value in the field used as parameter. Usually the count parameter is * (all fields) so only if all fields of row are NULLs this row will not be considered

In the above example if we have another row like this:

``````106    Italian    NULL
``````

This row will not be consider in count calculation

NOTE

The function `COUNT(*)` returns the number of rows in a table. This value can also be obtained by using a constant non-null expression that contains no column references, such as `COUNT(1)`.

Example

``````Select COUNT(1) From Marksheet
``````

## COUNT(Column_Name) with GROUP BY Column_Name

Most of the time we like to get the total number of occurrence of a column value in a table for example:

TABLE NAME : REPORTS

ReportNameReportPrice
Test10.00 \$
Test10.00 \$
Test10.00 \$
Test 211.00 \$
Test10.00 \$
Test 314.00 \$
Test 314.00 \$
Test 4100.00 \$
``````SELECT
ReportName AS REPORT NAME,
COUNT(ReportName) AS COUNT
FROM
REPORTS
GROUP BY
ReportName
``````
REPORT NAMECOUNT
Test4
Test 21
Test 32
Test 41

## MAX()

Returns the largest value in a given column.

We have table as shown in figure that will be used to perform different aggregate functions. The table name is Marksheet. ``````Select MAX(MarksObtained) From Marksheet
``````

## MIN()

Returns the smallest value in a given column.

We have table as shown in figure that will be used to perform different aggregate functions. The table name is Marksheet. ``````Select MIN(MarksObtained) From Marksheet
``````

## SUM()

Returns sum of numeric values in a given column.

We have table as shown in figure that will be used to perform different aggregate functions. The table name is Marksheet. ``````Select SUM(MarksObtained) From Marksheet
``````

The `sum` function doesn't consider rows with NULL value in the field used as parameter

In the above example if we have another row like this:

``````106    Italian    NULL
``````

This row will not be consider in sum calculation

2016-09-05
2017-01-09
Microsoft SQL Server Pedia
Icon