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:

CodePudding user response：

You're missing an extra join with the "orders_products" table, and the corresponding count of orders.

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

Note: if customers can buy the same product more than once, and you want to consider only products bought at least once, you need to stick an extra DISTINCT inside the count of the products as follows: COUNT(DISTINCT op.ProductID).

CodePudding user response：

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

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

CREATE TABLE products (
  productsId INTEGER PRIMARY KEY,
  ordersId INTEGER,
  CONSTRAINT FK_PersonProducts FOREIGN KEY (ordersId)
    REFERENCES orders(ordersId)
);

-- insert
INSERT INTO customer VALUES (0001);
INSERT INTO customer VALUES (0002);


INSERT INTO orders VALUES (100, 0001);
INSERT INTO orders VALUES (111, 0001);
INSERT INTO orders VALUES (200, 0002);

INSERT INTO products VALUES (300, 100);
INSERT INTO products VALUES (444, 111);
INSERT INTO products VALUES (500, 200);

-- 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.ordersId = op.ordersId
GROUP BY c.customerId 
ORDER BY TotalOrders DESC, 
         TotalProducts DESC