MySQL GROUP BY Statement

  1. Overview
  2. MYSQL
  3. MySQL SQL
  4. MySQL GROUP BY Statement

The GROUP BY statement in MySQL is used to group rows that have the same values into summary rows. This is commonly used to perform operations like counting, summing, or averaging for each group of records. For example, you might want to find the number of customers in each country.

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
  • column_name(s): Specifies the columns by which the rows should be grouped.
  • condition: The condition to filter the rows before grouping (optional).
  • ORDER BY: Orders the result by the grouped column or aggregate function (optional).

MySQL GROUP BY Examples

  1. Example 1: Counting the number of customers in each country

This query lists the number of customers per country:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
  • The query groups the rows by Country and counts the number of CustomerID for each country.
  1. Example 2: Number of customers in each country, sorted from high to low

To count the number of customers per country and order the result in descending order by the count:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
  • The query uses ORDER BY COUNT(CustomerID) DESC to sort the countries with the most customers at the top.

GROUP BY with JOIN Example

You can also use GROUP BY in conjunction with a JOIN to aggregate data from multiple tables.

Example: Number of orders sent by each shipper

This example counts the number of orders assigned to each shipper:

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
  • LEFT JOIN is used to get all shippers, even those who haven't shipped any orders.
  • COUNT(Orders.OrderID) counts the number of orders per shipper.
  • GROUP BY ShipperName groups the results by the ShipperName.

Key Points

  • GROUP BY is often used with aggregate functions (like COUNT(), SUM(), AVG(), MAX(), MIN()) to summarize the data.
  • When using GROUP BY, you must ensure that all non-aggregated columns in the SELECT clause are included in the GROUP BY clause, or the query will result in an error.
  • ORDER BY can be used after GROUP BY to control the order of the grouped results.

This powerful combination allows you to efficiently summarize and analyze data in MySQL.

Was this article helpful?