CODEWITHSUNDEEP
mysqlleft-join
Kanav
Kanav

Reputation: 2819

Joining two tables together with count

i have two tables named: orders and user_details

user_details table contains 4 columns: id, name, address, phone..

orders table contains 3 columns: order_id, id, order_date

id is common among the two tables.

Now i want to join these two tables in such a way that i get a table with one column (as name) and another column (as total number of orders).

i tried to use count:

SELECT name, COUNT(order_id) FROM orders
LEFT JOIN user_details
ON orders.id = user_details.id;

but i know it's wrong...What is the correct approach?

Upvotes: 0

Views: 61

Answers (3)

Jon B
Jon B

Reputation: 507

SELECT ud.name, count(o.order_id) FROM orders o
JOIN user_details ud
    ON o.id = ud.id
GROUP BY ud.id

Upvotes: 0

zzlalani
zzlalani

Reputation: 24394

You need to use GROUP BY

SELECT name, COUNT(order_id) FROM orders
LEFT JOIN user_details
ON orders.id = user_details.id
GROUP BY user_details.id;

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You should use the group function in order to use the aggregate functions

SELECT u.name, COUNT(o.order_id)
FROM orders o
LEFT JOIN user_details u
ON o.id = u.id /* make sure you have a correct relation b/w user and order table o.id should point to user id*/
GROUP BY  u.id

GROUP BY (Aggregate) Functions

Upvotes: 0

Related Questions