SQL with Manoj

 
Aggregate Functions provides us basic calculation over a group or a set of values. Aggregate functions are deterministic functions as they return the same value every time they are called by using a specific set of input values.

The Basic Aggregate Functions used day today in SQL Server are: COUNT, MIN, MAX, SUM, AVG.
 

You can use below query to create an Employee table for this demo:

USE [AdventureWorks2014]
GO

DROP TABLE IF EXISTS dbo.Employee

SELECT
    P.BusinessEntityID AS EmpID, P.Title, P.FirstName, P.MiddleName, P.LastName, 
    E.Gender, E.MaritalStatus, E.BirthDate AS DOB, E.HireDate AS DOJ, E.JobTitle, 
    D.Name AS DeptName, S.Rate
INTO dbo.Employee -- Create a new table here and load query data
FROM [Person].[Person] P
INNER JOIN [HumanResources].[Employee] E 
		ON E.BusinessEntityID = P.BusinessEntityID
CROSS APPLY (
    SELECT TOP 1 DepartmentID
    FROM [HumanResources].[EmployeeDepartmentHistory] DH 
    WHERE DH.BusinessEntityID = E.BusinessEntityID 
    ORDER BY StartDate DESC) EDH
INNER JOIN [HumanResources].[Department] D 
		ON D.DepartmentID = EDH.DepartmentID
INNER JOIN [HumanResources].[EmployeePayHistory] S 
		ON S.BusinessEntityID = P.BusinessEntityID

SELECT * FROM dbo.Employee -- 316
GO

1. COUNT() function returns the number of rows in a table or a group, and returns an INT (integer).

-- To get count of all reords in a table:
select COUNT(*) from dbo.Employee -- 316

-- Within Group: get count of records having Gender = 'M':
select COUNT(*) from dbo.Employee WHERE Gender = 'M' -- 228

-- Using DISTINCT with COUNT:
select COUNT(FirstName) from dbo.Employee -- 316
select COUNT(DISTINCT FirstName) from dbo.Employee -- 224

select COUNT(LastName) from dbo.Employee -- 316
select COUNT(DISTINCT LastName) from dbo.Employee -- 270

–> Using COUNT() function with GROUP BY

-- Get count of Employees in every Department:
select 
	DeptName, 
	COUNT(*) as EmpCount
from dbo.Employee
GROUP BY DeptName

2. MIN(), MAX() and AVG() functions returns the Minimum, Maximum and Average values from a table or a group.

-- Check the Minimum, Maximum and Average salaries of all Employes:
SELECT 
	MIN(Salary) as minSal, 
	MAX(Salary) as maxSal, 
	AVG(Salary) as avgSal
FROM dbo.Employee

–> Using MIN(), MAX(), AVG() functions with GROUP BY

-- Check Department wise Minimum, Maximum and Average salaries:
SELECT 
	DepartmentName, 
	MIN(Salary) as minSal, 
	MAX(Salary) as maxSal, 
	AVG(Salary) as avgSal
FROM dbo.Employee
GROUP BY DepartmentName

3. SUM() function returns the Sum of all values from a table or a group.

-- Check the budget of a company to pay their Employee's salary:
select SUM(Salary) as TotalSalary
from dbo.Employee

–> Using SUM() function with GROUP BY

-- Check Department wise budget of salary:
select 
	DepartmentName, 
	SUM(Salary) as TotalSalary
from dbo.Employee
GROUP BY DepartmentName

4. Using all Aggregate function together to get all information in a single query:

SELECT 
	COUNT(*)	as EmpCount, 
	MIN(Salary) as minSal, 
	MAX(Salary) as maxSal, 
	AVG(Salary) as avgSal, 
	SUM(Salary) as TotalSalary
FROM dbo.Employee

-- GROUP BY example with Aggregate functions:</strong>
SELECT 
	DepartmentName, 
	COUNT(*)	as EmpCount, 
	MIN(Salary) as minSal, 
	MAX(Salary) as maxSal, 
	AVG(Salary) as avgSal, 
	SUM(Salary) as TotalSalary
FROM dbo.Employee
GROUP BY DepartmentName

–> More Aggregate Functions in SQL Server are: COUNT_BIG, CHECKSUM_AGG, STDEV, STDEVP, GROUPING, GROUPING_ID, VAR, VARP. You can check them here: https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql