Mysql Count Number of rows

[ribrahim]

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.

[ehpratah]

Try looking at Inner join and count()

http://www.w3schools.com/sql/sql_join_inner.asp

[urtrivedi]

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

[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.