SQL SERVER

Aggregate Functions in SQL Server

In SQL Server by Baqir AliLeave a Comment

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.

FunctionDescription
COUNTReturn the total number of rows or records
AVGReturns the average of the values
MINReturns the minimum of the values in the result set
MAXReturns the maximum of the values in the result set
SUMReturns the sum of the values in the result set
STDEVReturns 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:

Group by in SQL SERVER

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:

With Rollup in SQL SERVER

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:

With Rollup in SQL SERVER

Support us by sharing this post

About the Author

Baqir Ali

Facebook Twitter

Hi, I am Baqir Ali ---- Blogger, Programmer, and a Teacher. I am currently working as a Programmer for Softnat Technologies. I have tons of ideas and data to share with you all via blogging.

Leave a Comment