# Aggregate Functions and Grouping

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:

```-- Find the Number of Employees

SELECT COUNT(*) AS NumEmployees
FROM Employees;```

Returns 9.

## Code Sample:

```-- 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:

```-- Find the Average Unit Price of Products

SELECT AVG(UnitPrice) AS AveragePrice
FROM Products;```

Returns 28.8663.

## Code Sample:

```-- 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:

```-- 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:

```/*
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:

```/*
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:

```/*
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.

```/*