This article describes how to use the `T-SQL Analytic functions`

in SQL Server database.

The Analytic functions perform a calculation on aggregate value based on a group of rows.

The `SQL Server`

analytic functions are: CUME_DIST, FIRST_VALUE, LAST_VALUE, LAG, LEAD, PERCENT_RANK, PERCENTILE_CONT and PERCENTILE_DISC.

The benefits of using analytic functions in SQL Server include the ability to calculate aggregate values over a partition of data,
calculate the total or cumulative sum of a set of data, calculate the average or mean of a set of data,
and calculate the standard deviation of a set of data.

These functions can be used to quickly and easily get the information that you need from your data.
Additionally, these functions can be used to calculate the aggregate values for a whole table, or for a partition of data within a table.

## CUME_DIST

SQL Server `CUME_DIST`

function calculates the relative position of a specified value in a group of values.

```
select c.*,
```

CUME_DIST () OVER (ORDER BY c.price) AS CumeDist

from courses c;

## FIRST_VALUE

SQL Server `FIRST_VALUE`

function returns the first value in an ordered set of values.

```
select c.*,
```

FIRST_VALUE(c.name) OVER (ORDER BY c.price ASC) AS FirstValue_Asc

from courses c order by c.price;

select c.*,

FIRST_VALUE(c.name) OVER (ORDER BY c.price DESC) AS FirstValue_Asc

from courses c order by c.price DESC;

## LAST_VALUE

SQL Server `LAST_VALUE`

function returns the last value in an ordered set of values.

```
select c.price, c.name, rc.id as reg_id,
```

FIRST_VALUE(c.name) OVER (ORDER BY c.price) AS FirstValue,

LAST_VALUE(c.name) OVER (ORDER BY c.price) AS LastValue

from courses c, register_course rc

where c.id=rc.course_id

order by rc.id;

## LAG

SQL Server `LAG`

function returns data from a previous row in the same result. First data value is null.

```
select c.*,
```

LAG(c.name) OVER (ORDER BY price) AS PrevCourse

from courses c;

## LEAD

SQL Server `LEAD`

function returns data from a next row in the same result. Last data value is null.

```
select c.*,
```

LEAD(c.name) OVER (ORDER BY price) AS NextCourse

from courses c;

## PERCENT_RANK

SQL Server `PERCENT_RANK`

function calculates the relative rank of a row within a group of rows.

```
select c.*,
```

PERCENT_RANK() OVER (ORDER BY c.price ) AS PctRank

from courses c;

## PERCENTILE_CONT

SQL Server `PERCENTILE_CONT`

function calculates a percentile based on a continuous distribution of the column value.

```
select distinct c.*,
```

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY c.id)

OVER (PARTITION BY c.price) AS PercentCount

from courses c, register_course rc

where c.id=rc.course_id;

## PERCENTILE_DISC

SQL Server `PERCENTILE_DISC`

function calculates a specific percentile for sorted values in an entire rowset.

```
select distinct c.*,
```

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY c.id)

OVER (PARTITION BY c.price) AS PercentDisc

from courses c, register_course rc

where c.id=rc.course_id;