Write Query Based on Two Tables That Returns Number of Orders From Each Customer Grouped by Email-CodePudding

I'm trying to write a query that considers the following tables that will return the number of orders submitted by each customer, grouped by their email.

Ex: [email protected] | 23

Tables

This is what I have so far:

SELECT COUNT(Order ID), Customer.Customer email
FROM Orders
INNER JOIN Customer ON Customer.Customer ID = Orders.Customer ID
GROUP BY Customer.Customer email
ORDER BY COUNT(Order ID)

I'm really struggling with SQL JOINS. Can anyone help me grasp this?

CodePudding user response：

You have a customer table where each row refers to a specific customer. You have an order table where a customer can have multiple orders.

When you join these two tables, that is, each row of the customer table is joined to each row of the order table that has a single customer ID.

For example, if a customer has two orders, he will be linked to the order table twice. Your table has a one-to-many relationship.

CodePudding user response：

SELECT C.CustomerID, COUNT(*) FROM Customers C, Orders O
WHERE C.CustomerID = O.CustomerID
GROUP BY C.CustomerID

I tried the above query in this website and looks like it worked.