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:

Support us by sharing this post