Viku Viku - 7 months ago 9
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
order_id
group by
cust_id
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

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;
Comments