Count total orders and total products for each customer

Question

I have 3 tables:

• customers (CustomerID)
• orders (OrderID, CustomerID)
• orders_products (OrderID, ProductID)

I need to select each customer with their total orders and total number of products ordered by them.

To count total orders by each customer, the query is:

SELECT c.CustomerID, COUNT(o.OrderID) AS TotalOrders 
FROM customers AS c 
INNER JOIN orders AS o ON c.CustomerID = o.CustomerID 
GROUP BY o.CustomerID 
ORDER BY TotalOrders DESC 

But how to count total number of products ordered by each customer?

I want something like below:

CustomerID	TotalOrders	TotalProducts
1	5	12
2	3	8

Question UPDATED with Sample Data:

I have updated my question with sample data of my three tables. The output of query suggested by some STO users here is also given below.

customers:

RowID	CustomerID
1	1
2	2

orders:

RowID	OrderID	CustomerID
1	101	1
2	102	1
3	103	2
4	104	1

orders_products:

RowID	OrderID	ProductID
1	101	1
2	101	2
3	102	1
4	103	1
5	104	1

Query:

SELECT c.CustomerID, COUNT(o.OrderID) AS TotalOrders, COUNT(op.ProductID) AS TotalProducts 
FROM customers AS c INNER JOIN orders AS o 
ON c.CustomerID = o.CustomerID 
INNER JOIN orders_products AS op 
ON o.OrderID = op.OrderID 
GROUP BY c.CustomerID 
ORDER BY TotalOrders DESC, TotalProducts DESC 

Output (Incorrect):

CustomerID	TotalOrders	TotalProducts
1	4	4
2	1	1

You can see that customer with ID 1 has placed 3 orders and 5 products purchased under these orders. But query gives 4 total orders and also 4 total products for him. That's wrong!

Expected Output (Correct):

CustomerID	TotalOrders	TotalProducts
1	3	4
2	1	1

Answer 1

I have 1st calculated total number of products per order using the sub-query. Then, I'm join the tables of orders_products, orders, & customers to calculate the total orders & total products for each customer.

SELECT 
       cust.CustomerID, 
       COUNT(DISTINCT ord.OrderID) AS TotalOrders, 
       SUM(OrdPrd.TotalProducts) AS TotalProducts

FROM customers cust

JOIN orders ord ON cust.CustomerID = ord.CustomerID
JOIN (
     SELECT OrderID, COUNT(ProductID) AS TotalProducts
     FROM orders_products
     GROUP BY OrderID
) 
OrdPrd ON ord.OrderID = OrdPrd.OrderID

GROUP BY cust.CustomerID
ORDER BY TotalOrders DESC, TotalProducts DESC;

Answer 2

we can calculate this by using joins,

step1: calculated number of products per each customer and named as x step2: calculated number of orders per each customer and named as y step3: do the self join considering x,y derived tables and select required columns. input_tables:

below is the script part:

select x.cust_id,no_of_orders,no_of_products 
from 
(
select o.cust_id ,count(p.order_id) as no_of_products  from Orders o
join products p 
on p.order_id = o.order_id group by o.cust_id
) as x ,
(
select o.cust_id ,count(o.order_id) as no_of_orders  from Orders o
join Customer c
on c.cust_id = o.cust_id group by o.cust_id
) as y 
where x.cust_id = y.cust_id

Answer 3

First ProductID is a primary key should be unique.
Second i try with different types of JOIN : INNER JOIN: Returns records that have matching values in both tables
LEFT JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
CROSS JOIN: Returns all records from both tables

But nothing there is a mistake on the relation between the table.

In my case I changed orders table and it contains ProductID and CustomerID and it gives the correct result, hope to help you

-- create
CREATE TABLE customer (
  customerId INTEGER PRIMARY KEY
);

CREATE TABLE orders (
  ordersId INTEGER PRIMARY KEY,
  customerId INTEGER,
  productsId INTEGER,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (customerId)
    REFERENCES customer(customerId),

  CONSTRAINT FK_PersonProducts FOREIGN KEY (productsId)
    REFERENCES products(productsId)  
);

CREATE TABLE products (
  productsId INTEGER PRIMARY KEY
);

-- insert
INSERT INTO customer VALUES (1);
INSERT INTO customer VALUES (2);


INSERT INTO orders VALUES (101, 1, 10001);
INSERT INTO orders VALUES (102, 1, 10001);
INSERT INTO orders VALUES (103, 2, 10005);
INSERT INTO orders VALUES (104, 1, 10003);


INSERT INTO products VALUES (10001);
INSERT INTO products VALUES (10002);
INSERT INTO products VALUES (10003);
INSERT INTO products VALUES (10004);
INSERT INTO products VALUES (10005);

-- fetch 
SELECT c.customerId, 
       COUNT(o.ordersId) AS TotalOrders,
       COUNT(op.productsId) AS TotalProducts
FROM customer AS c 
INNER JOIN orders AS o ON c.customerId = o.customerId 
INNER JOIN products AS op ON o.productsId = op.productsId
GROUP BY c.customerId