Aggregate functions often need an added GROUP BY statement.
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Orders" table:
And a selection from the "Shippers" table:
And a selection from the "Employees" table:
|1||Davolio||Nancy||1968-12-08||EmpID1.pic||Education includes a BA....|
|2||Fuller||Andrew||1952-02-19||EmpID2.pic||Andrew received his BTS....|
|3||Leverling||Janet||1963-08-30||EmpID3.pic||Janet has a BS degree....|
Now we want to find the number of orders sent by each shipper.
The following SQL statement counts as orders grouped by shippers:
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
GROUP BY ShipperName;
We can also use the GROUP BY statement on more than one column, like this:
SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
INNER JOIN Shippers
INNER JOIN Employees
GROUP BY ShipperName,LastName;