0

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.

4
Contributors
3
Replies
30
Views
1 Year
Discussion Span
Last Post by diafol
1
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

Edited by urtrivedi

Votes + Comments
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

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.