Question 21148 in Mysql Question posted in
The official MySQL documentation can be found here.

Count total orders and total products for each customer – Mysql

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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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:
    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

    results

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search