Database: How To Use GROUP BY and ORDER BY in SQL

Structured Query Language (SQL) databases can store and manage a lot of data across numerous tables. With large data sets, it’s important to understand how to sort data, especially for analyzing result sets or organizing data for reports or external communications.

Two common statements in SQL that help with sorting your data are GROUP BY and ORDER BY.

GROUP BY:

GROUP BY is most commonly  used with SQL aggregate functions to compute statistics (such as a count of certain values, sum, average, and the minimum/maximum value in a set) for a group of rows. 

Example 1: Grouping by a single column and performing a COUNT:

Suppose we have a table named "orders" with columns "product_id" and "quantity_sold". We want to count the number of orders for each product.

Output:

This result shows the count of orders for each product.

Example 2: Using aggregate functions with GROUP BY:

Suppose we have a table named "employees" with columns "department" and "salary". We want to find the total salary expenditure for each department.

Output:

This result shows the total salary expenditure for each department.

ORDER BY:

Let’s talk about ORDER BY. This command sorts the query output in ascending (1 to 10, A to Z) or descending (10 to 1, Z to A) order. The ascending sort is the default; if you omit the ASCending or DESCending keyword, the query will be sorted in ascending order. You can specify the sort order using ASC or DESC. Here’s a simple example:

Output:

This query selects the movie name, the city where the movie is showing, and the gross earnings. Then the output is sorted by gross earnings using the ORDER BY clause.

Example 1: Ordering by a single column in ascending order:

Suppose we have a table named "students" with columns "student_id", "name", and "score". We want to retrieve the names and scores of all students and order them by their scores in ascending order.

Output:

This result shows the names and scores of all students ordered by their scores in ascending order.

Example 2: Ordering by multiple columns:

Suppose we have a table named "employees" with columns "department" and "salary". We want to retrieve the department and salary of all employees and order them first by department in ascending order and then by salary in descending order.

Output:

This result shows the department and salary of all employees ordered by department in ascending order and within each department, the salaries are ordered in descending order.

GROUP BY and ORDER BY Together:

Example 1: Grouping by a column and ordering by the aggregated value

Suppose we have a table named "employees" with columns "department" and "salary". We want to find the total salary expenditure for each department and then order the results by the total salary expenditure in descending order.

Output:

Example 2: Grouping by multiple columns and ordering by one of them

Suppose we have a table named "sales" with columns "product_name", "category", and "sales_amount". We want to find out the total sales amount for each product category in each city and then order the results by the city name.

Output: