Important SQL Commands for Sorting and Grouping (Chapter 4)

Welcome to my blog, where I will take you on a journey through the basics of two powerful SQL commands: GROUP BY and ORDER BY. These commands are essential for organizing and sorting data in your database. By the end of this blog, you’ll be equipped with the knowledge to manipulate data efficiently and gain valuable insights from your datasets. Let’s get started and explore the world of grouping and sorting in SQL!

‘LIMIT’ with ‘WHERE’ clause

The LIMIT clause is used to restrict the number of rows returned by a query. It is often used to display only a specific number of rows or to implement pagination.

SELECT column1, column2, ...
FROM table_name
WHERE condition
LIMIT number_of_rows;
-------------------------------
Example
SELECT id, name, department
FROM employees
WHERE department = 'HR'
LIMIT 5;
-------------------------------

‘ORDER BY’

the ORDER BY clause is used to sort the result set of a query based on one or more columns. It allows us to control the order in which the rows are returned, making it easier to read and analyze the data. The basic syntax of the ORDER BY clause is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

In the above syntax, table_name is the name of the table we want to query, and condition is the optional condition that filters the rows. The column1, column2, etc., represent the columns by which we want to sort the data. We can specify multiple columns separated by commas to define a hierarchical sorting order.

The ASC (Ascending) keyword is used to sort the data in ascending order (from the smallest value to the largest). It is the default sorting order if we do not explicitly specify ASC or DESC.

The DESC (Descending) keyword is used to sort the data in descending order (from the largest value to the smallest).

Here’s an example of using the ORDER BY clause:

Let’s say we have a table called students with columns student_id, first_name, last_name, and age.

SELECT student_id, first_name, last_name, age
FROM students
WHERE age >= 18
ORDER BY last_name ASC, first_name ASC;

We can use the ORDER BY clause with or without the LIMIT clause to control the number of rows returned and their sorting order.

‘GROUP BY’

The GROUP BY clause is used to group rows that have the same values in one or more columns. It is often used in conjunction with aggregate functions (such as SUM, COUNT, AVG, etc.) to perform calculations on groups of rows rather than individual rows. The basic syntax of the GROUP BY clause is as follows:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

Here’s an example of using the GROUP BY clause:

Let’s say we have a table called orders with columns order_id, customer_id, product_id, and amount.

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;

‘HAVING’ with ‘GROUP BY’

The HAVING clause is used in conjunction with the GROUP BY clause in SQL, and it allows us to filter the result set based on aggregated values. While the WHERE clause filters individual rows before grouping, the HAVING clause filters the groups of rows after the grouping has been done using the GROUP BY clause and aggregate functions.

The basic syntax of using the HAVING clause with GROUP BY is as follows:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2
HAVING condition;

Let’s say we have a table called orders with columns order_id, customer_id, product_id, and amount.

SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING total_amount > 1000;

In this example, the query calculates the total amount (total_amount) for each customer_id using the SUM aggregate function. Then, the HAVING clause filters out the groups where the total_amount is greater than 1000. As a result, only those customers who spent more than 1000 in total will be included in the final result set.

It’s important to note that the HAVING clause operates on aggregated values and can use any aggregate function in its conditions. We can use HAVING to filter groups based on conditions such as maximum values, minimum values, sums, averages, and other aggregate calculations.

Order of Execution in SQL

In SQL, the typical order of execution for a query is as follows:

1. FROM clause: Specifies the data sources (tables or views) for the query.
2. JOIN clause: A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
3. WHERE clause: Filters the data based on specified conditions.
4. GROUP BY clause: Groups the data based on specified columns for aggregation.
5. HAVING clause: Filters the grouped data based on aggregate conditions.
6. SELECT clause: Specifies the columns to be included in the result set.
7. ORDER BY clause: Sorts the result set based on specified columns.