AGGREGATION OF INFORMATION

Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

AVG() - Returns the average value

COUNT() - Returns the number of rows

FIRST() - Returns the first value

LAST() - Returns the last value

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum

Group By Clause

The GROUP BY statement is used in conjunction with the aggregate functions to group

the result-set by one or more columns.

Syntax:

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

Example:

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders

FROM Orders LEFT JOIN Shippers

ON Orders.ShipperID=Shippers.ShipperID

GROUP BY ShipperName;

The above SQL statement counts as orders grouped by shippers

Having Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used 

with aggregate functions.

Syntax:

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value;

Example:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders

FROM Orders INNER JOIN Employees

ON Orders.EmployeeID=Employees.EmployeeID

GROUP BY LastName

HAVING COUNT(Orders.OrderID) > 10;

The above SQL statement finds if any of the employees has registered more than 10 orders:

Problem Scenario

Write a query to get the employees who has orders more than 25 and their last name should be 

either 'Davolio' or 'Fuller'

Approach to solve the problem

Join Employee and Order Table use the aggregate function Count to find the order placed by the 

employee and use having clause to filter for the number of order that are more than 25 and use 

where clause for filtering based on the last name.

Solution

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders

FROM Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID

WHERE LastName='Davolio' OR LastName='Fuller'

GROUP BY LastName

HAVING COUNT(Orders.OrderID) > 25;

Explanation about the solution

Employee and Order table is joined using the EmployeeID foreign key in the order table (join 

condition here). Then restrict the results only for the employee whose last name is 'Davolio' or 

'Fuller' using a where clause. Then restrict the count of order place using a having clause. Then 

we have grouped by the last name.