GROUP BY with ORDER BY in SQL

Introduction

We use GROUP BY to group similar data together and ORDER BY to sort the results. Combining them helps us see grouped data in a sorted way.

When you want to count how many times each category appears and see the counts sorted.

When you want to find the total sales per product and list products from highest to lowest sales.

When you want to group students by their class and order the classes alphabetically.

When you want to group employees by department and order departments by the number of employees.

Syntax

SQL

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
ORDER BY column1 [ASC|DESC];

GROUP BY groups rows that have the same values in specified columns.

ORDER BY sorts the final grouped results by one or more columns, ascending (ASC) or descending (DESC).

Examples

Groups products by category and counts them, then sorts categories alphabetically.

SQL

SELECT category, COUNT(*)
FROM products
GROUP BY category
ORDER BY category ASC;

Groups employees by department, sums their salaries, and sorts departments by total salary from highest to lowest.

SQL

SELECT department, SUM(salary)
FROM employees
GROUP BY department
ORDER BY SUM(salary) DESC;

Groups customers by city, calculates average age, and sorts cities by average age from youngest to oldest.

SQL

SELECT city, AVG(age)
FROM customers
GROUP BY city
ORDER BY AVG(age) ASC;

Sample Program

This creates a sales table, adds some sales data, then groups sales by product, sums quantities, and orders products by total quantity sold from highest to lowest.

SQL

CREATE TABLE sales (
  product VARCHAR(20),
  quantity INT
);

INSERT INTO sales (product, quantity) VALUES
('Apple', 10),
('Banana', 5),
('Apple', 15),
('Banana', 7),
('Cherry', 20);

SELECT product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product
ORDER BY total_quantity DESC;

Important Notes

You can order by any column in the SELECT list, including aggregated columns.

ORDER BY happens after GROUP BY, so it sorts the grouped results, not the original rows.

If you don't specify ASC or DESC, ORDER BY defaults to ascending order.

Summary

GROUP BY groups rows with the same values in specified columns.

ORDER BY sorts the grouped results by one or more columns.

Use GROUP BY with ORDER BY to see grouped data in a sorted and clear way.