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

COUNT()

Returns the number of rows containing nonNULL values in the specified field. 
SUM()

Returns the sum of the nonNULL values in the specified field. 
AVG()

Returns the average of the nonNULL values in the specified field. 
MAX()

Returns the maximum of the nonNULL values in the specified field. 
MIN()

Returns the minimum of the nonNULL values in the specified field. 
 Find the Number of Employees SELECT COUNT(*) AS NumEmployees FROM Employees;
Returns 9.
 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.
 Find the Average Unit Price of Products SELECT AVG(UnitPrice) AS AveragePrice FROM Products;
Returns 28.8663.
 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.
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.
 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:
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.
/* 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:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
/* 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:
SELECT
clause must also appear in the GROUP BY
clause.HAVING
clause.ORDER BY
clause.HAVING
clause.ORDER BY
clause.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.
/* 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.
/* Find out in how many different cities Northwind has employees. */ SELECT COUNT(DISTINCT City) AS NumCities FROM Employees