Aggregate Functions and Grouping

Contact Us or call 1-877-932-8228
Aggregate Functions and Grouping

Aggregate Functions and Grouping

Aggregate Functions

Aggregate functions are used to calculate results using field values from multiple records. There are five common aggregate functions.

Common Aggregate Functions
Aggregate Function Description
COUNT() Returns the number of rows containing non-NULL values in the specified field.
SUM() Returns the sum of the non-NULL values in the specified field.
AVG() Returns the average of the non-NULL values in the specified field.
MAX() Returns the maximum of the non-NULL values in the specified field.
MIN() Returns the minimum of the non-NULL values in the specified field.

Code Sample:

AdvancedSelects/Demos/Aggregate-Count.sql
-- Find the Number of Employees

SELECT COUNT(*) AS NumEmployees
FROM Employees;

Returns 9.

Code Sample:

AdvancedSelects/Demos/Aggregate-Sum.sql
-- Find the Total Number of Units Ordered of Product ID 3

/******************************
SQL Server
******************************/
SELECT SUM(Quantity) AS TotalUnits
FROM "Order Details"
WHERE ProductID=3;

/******************************
Oracle and MySQL
******************************/
SELECT SUM(Quantity) AS TotalUnits
FROM Order_Details
WHERE ProductID=3;

Returns 328.

Code Sample:

AdvancedSelects/Demos/Aggregate-Avg.sql
-- Find the Average Unit Price of Products

SELECT AVG(UnitPrice) AS AveragePrice
FROM Products;

Returns 28.8663.

Code Sample:

AdvancedSelects/Demos/Aggregate-MinMax.sql
-- Find the Earliest and Latest Dates of Hire

SELECT MIN(HireDate) AS FirstHireDate,
	MAX(HireDate) AS LastHireDate
FROM Employees;

The above SELECT statement will return April 1, 1992 and November 15, 1994 as the FirstHireDate and LastHireDate, respectively. The date format will vary from database to database.

Grouping Data

GROUP BY

With the GROUP BY clause, aggregate functions can be applied to groups of records based on column values. For example, the following code will return the number of employees in each city.

Code Sample:

AdvancedSelects/Demos/Aggregate-GroupBy.sql
-- Retrieve the number of employees in each city

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City;

The above SELECT statement will return the following results:

HAVING

The HAVING clause is used to filter grouped data. For example, the following code specifies that we only want information on cities that have more than one employee.

Code Sample:

AdvancedSelects/Demos/Aggregate-Having.sql
/*
	Retrieve the number of employees in each city 
	in which there are at least 2 employees.
*/

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
GROUP BY City
HAVING COUNT(EmployeeID) > 1;

The above SELECT statement will return the following results:

Order of Clauses

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

Code Sample:

AdvancedSelects/Demos/Aggregate-OrderOfClauses.sql
/*
	Find the number of sales representatives in each city that contains 
	at least	2 sales representatives. Order by the number of employees.
*/

SELECT City, COUNT(EmployeeID) AS NumEmployees
FROM Employees
WHERE Title = 'Sales Representative'
GROUP BY City
HAVING COUNT(EmployeeID) > 1
ORDER BY NumEmployees;

The above SELECT statement will return the following results:

Grouping Rules

  • Every non-aggregate column that appears in the SELECT clause must also appear in the GROUP BY clause.
  • You may not use aliases in the HAVING clause.
  • You may use aliases in the ORDER BY clause.
  • You may only use calculated fields in the HAVING clause.
  • You may use calculated field aliases or actual fields in the ORDER BY clause.

Selecting Distinct Records

The DISTINCT keyword is used to select distinct combinations of column values from a table. For example, the following example shows how you would find all the distinct cities in which Northwind has employees.

Code Sample:

AdvancedSelects/Demos/Distinct.sql
/*
Find all the distinct cities in which Northwind has employees.
*/

SELECT DISTINCT City
FROM Employees
ORDER BY City

DISTINCT is often used with aggregate functions. The following example shows how DISTINCT can be used to find out in how many different cities Northwind has employees.

Code Sample:

AdvancedSelects/Demos/Distinct-Count.sql
/*
Find out in how many different cities Northwind has employees.
*/

SELECT COUNT(DISTINCT City) AS NumCities
FROM Employees
Next