SHASHANGKA SHEKHAR'S BLOG

Hi, I am Shashangka Shekhar, Working with Microsoft Technologies. This is my personal blog where I share some of my experiences.

Aggregate Functions in SQL Server

Let’s create a SampleDB database fist with SQL Management Studio. Now create a table in SampleDB.

Insert Sample Data:

Aggregate Functions in SQL Server

COUNT (Transact-SQL)

Returns the number of items in a group. COUNT always returns an int data type value.

Syntax:

COUNT (*): The following example finds the total number of employees.

COUNT (DISTINCT expression): The following example lists the number of different City

COUNT (*)  With other aggregates: The following example shows that COUNT(*) can be combined with other aggregate functions in the select list.

COUNT _BIG(Transact-SQL)

Returns the number of items in a group. COUNT_BIG always returns an bigint data type value.

COUNT_BIG (*): The following example finds the total number of employees.

COUNT_BIG (DISTINCT expression): The following example lists the number of different City

COUNT_BIG (*)  With other aggregates: The following example shows that COUNT(*) can be combined with other aggregate functions in the select list.

AVG (Transact-SQL)

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

USING Sum() and Avg() : The following example calculates the average age and the sum of salary those who lives in dhaka

USING Sum() and Avg() with Group By : When used with a GROUP BY clause, each aggregate function produces a single value for each group, instead of for the whole table.

USING Avg() and DISTINCT : The following statement returns the average Salary.By specifying DISTINCT, only unique values are considered in the calculation.

USING Avg() without DISTINCT : The following statement returns the average Salary. This time it’ll consider duplicate values in the calculation.

USING Avg() and OVER: The following example uses the AVG function with the OVER clause to provide a average age of Employees. The data is partitioned by Emp_Gender and logically

MAX (Transact-SQL)

Returns the maximum value in the expression.

USING Max(): The following example returns the highest (maximum) age

USING Max() and OVER:

 

MIN (Transact-SQL)

Returns the minimum value in the expression.

MIN is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses.

USING Min(): The following example returns the lowest(minimum) age

USING Min() and OVER:

 

SUM (Transact-SQL)

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

USING Sum(): The following examples shows using the SUM function to return summary data

USING Sum() and OVER:

 

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 <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.

USING Grouping(): The following example groups Emp_City and aggregates Salary amounts

 

Post a comment