Post navigation

PHP SQL Server – Aggregate Functions

PHP SQL Server – Aggregate Functions

What are Aggregate Functions in SQL Server? This article helps you to explore various Aggregate Functions in SQL Server.

Introduction

What are Aggregate Functions?

I can give different definitions.

1. Aggregate functions are built in sql server functions.
2. Aggregate functions are applied to sets of records rather than to a single record.
3. Aggregate functions performs a computation on a set of values rather than on a single value.
4. Aggregate functions uses to summarize data.
5. Aggregate functions perform a calculation on a set of values and return a single value.

Getting Started

The information in multiple records are processed in a particular manner and then displayed in a single record answer.
Aggregate functions are often used in conjuction with GROUP BY clause.
Aggregate functions cannot be nested. The expression cannot be a subquery.

The same as CHECKSUM, but the primary difference is that CHECKSUM is oriented around rows,
whereas CHECKSUM_AGG is oriented around columns.

The syntax: CHECKSUM( [ALL | DISTINCT] <expression> )

SELECT CHECKSUM_AGG(CAST(UnitPrice AS int)) FROM dbo.[Order Details]

update dbo.[Order Details] set UnitPrice = 15

where orderid = 10248 and ProductID = 11

SELECT CHECKSUM_AGG(CAST(UnitPrice AS int)) FROM dbo.[Order Details]

COUNT

Returns the number of items in expression. The data type returned is of type int.

The syntax: COUNT( [ALL | DISTINCT] <expression> | * )

select COUNT(*), AVG(UnitPrice) from dbo.[Order Details]

COUNT_BIG

Returns the number of items in a group. The data type returned is of type bigint.

The syntax: COUNT( [ALL | DISTINCT] <expression> | * )

select COUNT_BIG(*), AVG(UnitPrice) from dbo.[Order Details]

GROUPING

MSDN : Is an aggregate function that causes an additional column to be output with a value of 1 when the row
is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.Function adds an extra column to the output of a SELECT statement.