SQL - Group By vs Order By

SQL: Aggregating with GROUP BY vs. Sorting with ORDER BY

Two of the most fundamental clauses in SQL for data analysis are GROUP BY and ORDER BY. While they both help organize your results, they serve very different purposes. GROUP BY aggregates rows into summaries, while ORDER BY sorts the final output.

Setup: Sample Data

We will use a Sales table for our examples.

CREATE TABLE Sales (
    sale_id SERIAL PRIMARY KEY,
    product_category VARCHAR(50),
    region VARCHAR(50),
    sale_amount NUMERIC(10, 2),
    sale_date DATE
);

INSERT INTO Sales (product_category, region, sale_amount, sale_date) VALUES
('Electronics', 'North', 1200.00, '2023-10-01'),
('Books', 'South', 75.50, '2023-10-02'),
('Electronics', 'North', 850.00, '2023-10-03'),
('Clothing', 'West', 250.00, '2023-10-03'),
('Books', 'North', 120.00, '2023-10-04'),
('Electronics', 'South', 1500.00, '2023-10-05');

Aggregating Data with GROUP BY

The GROUP BY clause is used to arrange identical data into groups. Its primary purpose is to be used with aggregate functions (like COUNT(), SUM(), AVG()) to perform calculations on each group.

The Golden Rule of GROUP BY: When using GROUP BY, any column in your SELECT statement must either be part of the GROUP BY clause or be used inside an aggregate function.

Example: Total Sales per Category

Let’s group the rows by product_category and calculate the total sales for each.

SELECT
    product_category,
    SUM(sale_amount) AS total_sales
FROM Sales
GROUP BY product_category;

GROUP BY collapses the multiple rows for ‘Electronics’ and ‘Books’ into single summary rows:

product_category	total_sales
Clothing	250.00
Books	195.50
Electronics	3550.00

Sorting Results with ORDER BY

The ORDER BY clause is used to sort the final result set of your query. It does not change the data or the number of rows returned; it only affects the presentation order. You can sort in ascending (ASC, the default) or descending (DESC) order.

Example: List All Sales by Amount

To list all individual sales, from largest to smallest:

SELECT product_category, region, sale_amount
FROM Sales
ORDER BY sale_amount DESC;

This reorders all six rows of the table without grouping them.

Using GROUP BY and ORDER BY Together

You can, and often will, use both clauses in a single query. GROUP BY performs the aggregation first, and then ORDER BY sorts the resulting summary rows. The logical order of execution in a query is: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.

Example: Rank Categories by Total Sales

Let’s find the total sales per category, and then list the categories from most to least profitable.

SELECT
    product_category,
    SUM(sale_amount) AS total_sales
FROM Sales
GROUP BY product_category
ORDER BY total_sales DESC;

The query first groups and calculates sums, then sorts those results:

product_category	total_sales
Electronics	3550.00
Clothing	250.00
Books	195.50

The HAVING Clause: Filtering Groups

A quick note on HAVING: while WHERE filters rows before grouping, HAVING filters groups after they have been created. For instance, to see only categories with total sales over $1000:

SELECT
    product_category,
    SUM(sale_amount) AS total_sales
FROM Sales
GROUP BY product_category
HAVING SUM(sale_amount) > 1000;

Key Differences: A Summary

Aspect	GROUP BY	ORDER BY
Purpose	Aggregates rows into groups for summary calculations.	Sorts the final result set for presentation.
Effect on Rows	Reduces the number of rows returned.	Reorders rows, but does not change the number of rows.
Use with Functions	Used with aggregate functions (SUM, COUNT, etc.).	Does not require aggregate functions.
Position in Query	Appears before HAVING and ORDER BY.	Is almost always the last clause in a query.
Filtering	Groups are filtered using the HAVING clause.	Individual rows are filtered using the WHERE clause (before sorting).