SQL GROUP BY Statement

       

SQL GROUP BY

❮ PreviousNext ❯

---

SQL GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, and apply aggregate functions (such as COUNT, SUM, AVG, MAX, MIN, etc.) on each group. The GROUP BY clause is often used in combination with SELECT statements to perform calculations on groups of data. The syntax for using GROUP BY is as follows:

SELECT column1, column2, ..., aggregate_function(columnX)

FROM table_name

GROUP BY column1, column2, ...;

In this syntax:

• SELECT specifies the columns you want to retrieve from the SELECT statement, including any aggregate functions that you want to apply to the grouped data.
• FROM specifies the table you want to retrieve data from.
• GROUP BY is used to specify the columns by which you want to group the data. You can specify one or more columns by which to group the data.
• aggregate_function() is the aggregate function that you want to apply to the grouped data, such as COUNT(), SUM(), AVG(), MAX(), MIN(), etc. These functions perform calculations on the grouped data within each group.

For example, let's say you have a table called sales with columns product_name, sales_date, and sales_amount, and you want to calculate the total sales amount for each product. You can use GROUP BY with the SUM() aggregate function as follows:

SELECT product_name, SUM(sales_amount) as total_sales_amount

FROM sales

GROUP BY product_name;

This query will group the sales data by product_name, and calculate the total sales amount for each product using the SUM() aggregate function. The result set will include the product_name and the calculated total_sales_amount for each product. Note that when using GROUP BY, you can only select columns that are either part of the GROUP BY clause or are used with an aggregate function.

SQL GROUP BY Examples 

The following SQL statement lists the number of customers in each country: 

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country; 

The following SQL statement lists the number of customers in each country, sorted high to low: 

Example 

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

ORDER BY COUNT(CustomerID) DESC; 

GROUP BY With JOIN Example 

The following SQL statement lists the number of orders sent by each shipper: 

Example 

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

LEFT JOIN Shippers 

ON Orders.ShipperID = Shippers.ShipperID 

GROUP BY ShipperName;

❮ PreviousNext ❯

---