Aggregate Functions in SQL Server

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

CREATE TABLE [dbo].[tblEmployee](
        [Emp_ID] [int] NOT NULL,
        [Emp_Name] [varchar](max) NOT NULL,
        [Emp_Age] [int] NOT NULL,
        [Emp_Salary] [int] NOT NULL,
        [Emp_City] [varchar](max) NOT NULL,
        [Emp_Gender] [char](1) NOT NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED 
(
        [Emp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Insert Sample Data:

INSERT INTO [dbo].[tblEmployee]

SELECT 1,'Shashangka',20,35000,'Dhaka',1 UNION ALL
SELECT 2,'Shekhar',     19,     40000,  'Bagerhat',     1 UNION ALL
SELECT 3,'Shamim',      21,     35000,  'Dhaka',        0 UNION ALL
SELECT 4,'Shariar',     20,     40000,  'Rajshahi',     1 UNION ALL
SELECT 5,'Rajib',       22,     35000,  'Rajshahi',     0 UNION ALL
SELECT 6,'Avishek',     21,     35000,  'Barishal',     1 UNION ALL
SELECT 7,'Asif',        23,     40000,  'Dhaka',        0

COUNT (Transact-SQL)

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

Syntax:

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

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

SELECT COUNT(*) FROM [dbo].[tblEmployee]
GO

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

SELECT COUNT(DISTINCT Emp_City)
FROM [dbo].[tblEmployee]
GO

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

SELECT COUNT(*), AVG(Emp_Age)
FROM [dbo].[tblEmployee]
WHERE Emp_Salary > 35000;
GO

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.

SELECT COUNT_BIG(*) FROM [dbo].[tblEmployee]
GO

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

SELECT COUNT_BIG(DISTINCT Emp_City)
FROM [dbo].[tblEmployee]
GO

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

SELECT COUNT_BIG(*), AVG(Emp_Age)
FROM [dbo].[tblEmployee]
WHERE Emp_Salary > 35000;
GO

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

SELECT AVG(Emp_Age)AS 'Average Employee Age', 
    SUM(Emp_Salary) AS 'Total Salary'
FROM [dbo].[tblEmployee]
WHERE Emp_City LIKE '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.

SELECT AVG(Emp_Age)as 'Average Employee Age', SUM(Emp_Salary) as 'Total Salary' ,Emp_City
FROM [dbo].[tblEmployee]
GROUP BY Emp_City;
GO

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

SELECT AVG(DISTINCT Emp_Salary)
FROM [dbo].[tblEmployee]

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

SELECT AVG(Emp_Salary)
FROM [dbo].[tblEmployee]

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

SELECT AVG(Emp_Age) OVER (PARTITION BY Emp_Gender ORDER BY Emp_Gender) AS Gender , Emp_Gender 
FROM [dbo].[tblEmployee]

MAX (Transact-SQL)

Returns the maximum value in the expression.

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

SELECT MAX(Emp_Age)
FROM [dbo].[tblEmployee]
GO

USING Max() and OVER:

SELECT MAX(Emp_Age) OVER (PARTITION BY Emp_Gender ORDER BY Emp_Gender) AS Gender , Emp_Gender 
FROM [dbo].[tblEmployee]
GO

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

SELECT MIN(Emp_Age)
FROM [dbo].[tblEmployee]
GO

USING Min() and OVER:

SELECT MIN(Emp_Age) OVER (PARTITION BY Emp_Gender ORDER BY Emp_Gender) AS Gender , Emp_Gender 
FROM [dbo].[tblEmployee]
GO

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

SELECT Emp_City, SUM(Emp_Salary)
FROM [dbo].[tblEmployee]
WHERE Emp_City IS NOT NULL 
    AND Emp_Salary != 0.00 
    --AND Emp_City LIKE 'Dhaka%'
GROUP BY Emp_City
ORDER BY Emp_City;
GO

USING Sum() and OVER:

SELECT Emp_City, SUM(Emp_Salary) OVER (PARTITION BY Emp_Gender ORDER BY Emp_Gender) AS Sal
FROM [dbo].[tblEmployee]
GO

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

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

SELECT ISNULL(Emp_City,'TOTAL') Emp_City, SUM(Emp_Salary) 'TotalSalary', GROUPING(Emp_City) AS 'Grouping'
FROM [dbo].[tblEmployee]
GROUP BY Emp_City WITH ROLLUP;
GO

SHASHANGKA SHEKHAR'S BLOG

Aggregate Functions in SQL Server