Aggregate Functions in SQL Server

What are Aggregate Functions in SQL SERVER?

Aggregate functions are some built-in functions by SQL SERVER that performs calculations on any set of data. Aggregate Functions return a single value as a result.

There are some functions that are more commonly used in queries. They are given below.

Function	Description
COUNT	Return the total number of rows or records
AVG	Returns the average of the values
MIN	Returns the minimum of the values in the result set
MAX	Returns the maximum of the values in the result set
SUM	Returns the sum of the values in the result set
STDEV	Returns the standard deviation of the values in the result set

In order to use these functions, we are using the AdventureWorks2014 Database by Microsoft. Which is a complete database to practice your T-SQL commands.

You can download the database from the link below

Download Adventure Works Database

The instructions to use are also given.

Example 1: Find Average, Minimum, and Maximum of List Price

select 
AVG(ListPrice) as 'Average',
MIN(ListPrice) as 'Minimum',
MAX(ListPrice) as 'Maximum'
from Production.Product
Where ListPrice<>0

Group By Clause With Aggregate Function in SQL SERVER

Group By Clause is used with aggregate functions. Group by clause returns the subtotal for each aggregated data.

Suppose what if I wanted to count that how many items are available in each color? There comes the use of group by clause.

Select P.Color, Sum(I.Quantity) as Total
From Production.Product P
INNER JOIN Production.ProductInventory I
On P.ProductID=I.ProductID
group by P.Color

It sums the total stock from inventory and groups them by color.

Output:

Please don’t calculate the Null Color.

Select P.Color, Sum(I.Quantity) as Total
From Production.Product P
INNER JOIN Production.ProductInventory I
On P.ProductID=I.ProductID
Where P.Color IS NOT NULL
group by P.Color

WITH ROLLUP Keyword With Aggregate Function in SQL SERVER

WITH ROLLUP Keyword is used to show subtotals or grand totals for more than one column. In order to use With Rollup Lets Create a new Table. Run the following code.

CREATE TABLE EmployeeRecord
(EmpNo INT NOT NULL, EmpName VARCHAR(10),
EmpPost VARCHAR(9), ManagerID INT,
Salery INT, COMM INT, DeptNO INT);
INSERT INTO EmployeeRecord
VALUES (7369, 'SMITH', 'CLERK', 7902, 800, NULL, 20);
INSERT INTO EmployeeRecord
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, 1600, 300, 30);
INSERT INTO EmployeeRecord
VALUES (7521, 'WARD', 'SALESMAN', 7698, 1250, 500, 30);
INSERT INTO EmployeeRecord
VALUES (7566, 'JONES', 'MANAGER', 7839, 2975, NULL, 20);
INSERT INTO EmployeeRecord
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, 1250, 1400, 30);
INSERT INTO EmployeeRecord
VALUES (7698, 'BLAKE', 'MANAGER', 7839, 2850, NULL, 30);
INSERT INTO EmployeeRecord
VALUES (7782, 'CLARK', 'MANAGER', 7839, 2450, NULL, 10);
INSERT INTO EmployeeRecord
VALUES (7788, 'SCOTT', 'ANALYST', 7566, 3000, NULL, 20);
INSERT INTO EmployeeRecord
VALUES (7839, 'KING', 'PRESIDENT', NULL, 5000, NULL, 10);
INSERT INTO EmployeeRecord
VALUES (7844, 'TURNER', 'SALESMAN', 7698, 1500, 0, 30);
INSERT INTO EmployeeRecord
VALUES (7876, 'ADAMS', 'CLERK', 7788, 1100, NULL, 20);
INSERT INTO EmployeeRecord
VALUES (7900, 'JAMES', 'CLERK', 7698, 950, NULL, 30);
INSERT INTO EmployeeRecord
VALUES (7902, 'FORD', 'ANALYST', 7566, 3000, NULL, 20);
INSERT INTO EmployeeRecord
VALUES (7934, 'MILLER', 'CLERK', 7782, 1300, NULL, 10);

Output:

Here I want to know how much each department gets Salary. Show me Subtotal for each Department and as well as Grand Total.

Select DeptNO,EmpPost,sum(Salery) as Salary
from EmployeeRecord
group by DeptNo, EmpPost
WITH ROLLUP;

Output: