WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL GROUP BY Statement


Aggregate functions often need an added GROUP BY statement.


The 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.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 2011-04-01 3
10249 81 6 2005-04-09 1
10250 34 4 1993-01-13 2

And a selection from the "Shippers" table:

ShipperID ShipperName Phone
1 India Express 1800 444 55555
2 Usa express (502) 444-3199
3 American Shipping (502) 444-9931

And a selection from the "Employees" table:

EmployeeID LastName FirstName BirthDate Photo Notes
1 Androw Aderson 1982-12-08 EmpID1.pic Education includes a BS
2 Carmalle Rx 1994-02-19 EmpID2.pic Andrew received his BCA
3 Karma Rx 1983-08-30 EmpID3.pic Janet has a MCA

SQL GROUP BY Example

Now we want to find the number of orders sent by each shipper.

The following SQL statement counts as orders grouped by shippers:

Example

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

GROUP BY More Than One Column

We can also use the GROUP BY statement on more than one column, like this:

Example

SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;