SQL GROUP BY and simple examples, sqlgroup

SQL GROUP BY and simple examples, sqlgroup

The group by statement is used in combination with the Aggregate function to GROUP result sets based on one or more columns.

SQL GROUP BY syntax

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name;

Demo Database

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

The data from the "Orders" table is as follows:

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10248	90	5	1996-07-04	3
10249	81	6	1996-07-05	1
10250	34	4	1996-07-08	2

Data from the "Shippers" table:

ShipperID	ShipperName	Phone
1	Speedy Express	(503) 555-9831
2	United Package	(503) 555-3199
3	Federal Shipping	(503) 555-9931

Data from the "Employees" table:

EmployeeID	LastName	FirstName	BirthDate	Photo	Notes
1	Davolio	Nancy	1968-12-08	EmpID1.pic	Education between des 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 ....

 SQL GROUP BY instance

Now we want to find the number of orders for delivery by each courier.

The following SQL statement is used to sort orders by Deliveryman:

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM OrdersLEFT JOIN ShippersON Orders.ShipperID=Shippers.ShipperIDGROUP BY ShipperName;

Group by more than one column

You can also apply the group by statement to more than one column, as shown below:

SELECT Shippers.ShipperName, Employees.LastName,COUNT(Orders.OrderID) AS NumberOfOrdersFROM ((OrdersINNER JOIN ShippersON Orders.ShipperID=Shippers.ShipperID)INNER JOIN EmployeesON Orders.EmployeeID=Employees.EmployeeID)GROUP BY ShipperName,LastName;

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!