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.

enter image description here

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.

enter image description here

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.

enter image description here

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.

enter image description here

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.

enter image description here

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