How to sort result from one SQL query with data from other table

Question

I have a database with two tables. In one table I have the information of costumers with a column costumer_id and a column costumer_name and I have a table with customer orders with columns order_id and customer_id. The tables look like this:

Customers:

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | Joe           |
|           2 | Michael       |
|           3 | Ben           |
+-------------+---------------+

Orders:

+----------+-------------+
| order_id | customer_id |
+----------+-------------+
|        1 |           1 |
|        2 |           2 |
|        3 |           3 |
+----------+-------------+

I now have the following query, which gives me the amount of orders from each costumer. The results are ordered by customer_id.

SELECT customer_id, COUNT(order_id) 
FROM Orders 
GROUP BY customer_id 
ORDER BY customer_id ASC;

Now I want the result to be ordered by the customer_name from the Customer table with a query. How can I achieve this?

VBoka VBoka · Accepted Answer · 2019-11-05T20:22:47

You can have it with the ID:

SELECT c.customer_name name
       , COUNT(order_id) sumarum
       , o.customer_id id
FROM Orders O
left join Customers C
on o.customer_id = c.customer_id
GROUP BY customer_name, o.customer_id
ORDER BY c.customer_name, o.customer_id;

Or without the ID (Just name and count):

select name
       , sumarum
from (SELECT o.customer_id id
       , COUNT(order_id) sumarum
       , c.customer_name name
FROM Orders O
left join Customers C
on o.customer_id = c.customer_id
GROUP BY customer_name, o.customer_id
ORDER BY c.customer_name, o.customer_id);

Here is the DEMO

How to sort result from one SQL query with data from other table

1 Answers