Aggregate Functions in SQL Server || AVG, COUNT, GROUPING functions in SQL

This video will provide you the details of each Aggregate functions in SQL Server 2014. I'll be walking through each and every kinds of build in function which are available in SQL Server in my next videos.
********************************************************************************
Aggregate Functions (Transact-SQL)

Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

Aggregate functions can be used as expressions only in the following:
• The select list of a SELECT statement (either a subquery or an outer query).
• A HAVING clause.

********************************************************************************
AVG (Transact-SQL)

Returns the average of the values in a group. Null values are ignored.

 Syntax

AVG ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )

 Arguments
ALL
Applies the aggregate function to all values. ALL is the default.
DISTINCT
Specifies that AVG be performed only on each unique instance of a value, regardless of how many times the value occurs.
expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.
OVER ( [ partition_by_clause ] order_by_clause)
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed.order_by_clause is required.

 Return Types
The return type is determined by the type of the evaluated result of expression.
Expression result Return type
tinyint int
smallint int
int int
bigint bigint
decimal category (p, s) decimal(38, s) divided by decimal(10, 0)
money and smallmoney category money
float and real category float










********************************************************************************


COUNT (Transact-SQL)

Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.

 Syntax
-- Syntax for SQL Server and Azure SQL Database

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
[ OVER (
[ partition_by_clause ]
[ order_by_clause ]
[ ROW_or_RANGE_clause ]
) ]

 Arguments
ALL
Applies the aggregate function to all values. ALL is the default.
DISTINCT
Specifies that COUNT returns the number of unique nonnull values.
expression
Is an expression of any type except text, image, or ntext. Aggregate functions and subqueries are not permitted.
*
Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.
OVER ( [ partition_by_clause ] [ order_by_clause ] [ ROW_or_RANGE_clause ] )
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed.

 Return Types
int


********************************************************************************




GROUPING (Transact-SQL)

Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT list, HAVING, and ORDER BY clauses when GROUP BY is specified.

 Syntax

GROUPING ( column_expression)

 Arguments

Is a column or an expression that contains a column in a GROUP BY clause.

 Return Types
tinyint