When working with SQL, you might encounter such situations where you want to group the data by a specific column and then order the results by the count of each group.
For example, let’s say you have a table of customer orders and you want to determine which products are the most popular. You can group the orders by product and then order the results by the count of orders for each product.
In this tutorial, we will learn how to perform an order by count operation in SQL by combining the built-in SQL operations.
Sample Table:
For this tutorial, we use a dummy table with random data as shown in the following queries:
order_id INT PRIMARY KEY,
product VARCHAR(50),
quantity INT,
order_date DATE
);
INSERT INTO orders (order_id, product, quantity, order_date)
VALUES
(1, 'Product A', 10, '2022-01-01'),
(2, 'Product B', 5, '2022-01-02'),
(3, 'Product A', 15, '2022-01-03'),
(4, 'Product C', 8, '2022-01-04'),
(5, 'Product B', 12, '2022-01-05'),
(6, 'Product A', 20, '2022-01-06'),
(7, 'Product B', 7, '2022-01-07'),
(8, 'Product C', 9, '2022-01-08'),
(9, 'Product A', 18, '2022-01-09'),
(10, 'Product B', 3, '2022-01-10');
select * from orders;
The resulting table is as follows:
SQL Order by Count
To order the SQL query results by count, we can combine the “GROUP BY” clause and the ORDER BY clauses.
Step 1: Select the Columns
The first step is to select the columns that we wish to group by and the columns that we want to count.
For example, if we have an orders table with two columns, “product” and “quantity”, we can group the orders based on the products column and count the number of orders for each product.
An example query is as follows:
FROM orders
GROUP BY product;
The previous query groups the orders by product and counts the orders for each product. We use the “AS” keyword as an alias for the “count” column for easier readability.
The resulting table is as follows:
Step 2: Order the Result by Count
Once we select the columns, we can proceed and order the results based on the count value. We can do this using the ORDER BY clause followed by the name of the count column.
We can also specify whether we want to order the results based on ascending or descending order.
An example query is as follows:
FROM orders
GROUP BY product
ORDER BY count DESC;
The previous query then groups the orders by product, counts the number of orders for each product, and then orders the results by the count in descending order.
An example resulting table is as follows:
To order in ascending order, run the following command:
FROM orders
GROUP BY product
ORDER BY count ASC;
Output:
Conclusion
We explored how we can combine the GROUP BY and the ORDER BY clause to order the results of a query based on the count.
