Dear Member,

I'm using MYSQL 5.6 and have 3 tables:
1. Customers (Cutomer_id, Customer_Name)
2. Order1 (Order_Id1, Customer_id)
3. Order2 (Order_Id2, Customer_id)
I need to write a query to count the number rows from Order1 and Order2 group by Customer_Id.
The result should be like:

Customer_Id        Customer_Name       Number of Orders (From Order1 and Order2)
0000000000          xxxxxxxxxxxxxxx      50

With best regards.

select a.Cutomer_id, a.Customer_Name, b.total1, c.total2 from  Customers a
left outer join (select customer_id, count(*) total1 from order1 group by customer_id) b on a.customer_id=b.customer_id
left outer join (select customer_id, count(*) total2 from order2 group by customer_id) c on a.customer_id=c.customer_id
commented: Thank you for the select statment, but i need the total to be shown as one value not two. Your select statement will result two values tot1 and Tot2. +0
Member Avatar for diafol

Seems like a bit of a strange schema to me. Why separate order1 and order2? Maybe easier to have one table with a 'type' flag column. It would certainly make this query a lot easier :) Anyway, I'm assuming that order_id1 and customer_id make the PK in Orders1 and that they are both FKs.

SELECT c.Cutomer_id, c.Customer_Name, COUNT(o.Customer_id) AS total FROM Customers AS c 
INNER JOIN 
(SELECT Customer_id FROM Orders1 
    UNION ALL 
SELECT Customer_id FROM Orders2) AS o 
ON c.Cutomer_id = o.Customer_id 
GROUP BY c.Cutomer_id

N.B. You spelled "customer" as "cutomer" in your OP, so am assuming this is correct. Change in the SQL if not.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.