Viku Viku - 1 year ago 75
SQL Question

MySQL SELECT from two tables with COUNT

i have two tables as below:

Table 1 "customer" with fields "Cust_id", "first_name", "last_name" (10 customers)
Table 2 "cust_order" with fields "order_id", "cust_id", (26 orders)

I need to display
"Cust_id" "first_name" "last_name" "order_id"

to where i need count of
group by
like list total number of orders placed by each customer.

I am running below query, however, it is counting all the 26 orders and applying that 26 orders to each of the customer.

SELECT COUNT(order_id), cus.cust_id, cus.first_name, cus.last_name
FROM cust_order, customer cus
GROUP BY cust_id;

Could you please suggest/advice what is wrong in the query?

Answer Source

You could try this one:

SELECT COUNT(cus_order.order_id), cus.cust_id, cus.first_name, cus.last_name 
FROM cust_order cus_order, customer cus 
WHERE cus_order.cust_id = cus.cust_id
GROUP BY cust_id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download