CODEWITHSUNDEEP
mysql
newpdv
newpdv

Reputation: 462

Joining tables and aggregating data in MySQL?

I have 2 tables. Members and their projects. I need to extract all the users, with the number of their projects, sorted by the number of projects.

Table: users:

id | username | email | password | reg_date

Table: projects:

id | title    | descr | autor

For the join:

projects.autor = users.id

Upvotes: 0

Views: 56

Answers (2)

Andi T
Andi T

Reputation: 616

SELECT u.id AS id, u.username AS username, u.email AS email, u.password AS password, u.reg_date AS reg_date, COUNT(p.id) AS projects_count 
FROM users u
LEFT OUTER JOIN projects p ON p.autor = u.id
GROUP BY u.id 
ORDER BY projects_count DESC

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

SELECT
  users.id,
  users.username,
  COUNT(projects.id) AS `num_projects`
FROM
  users
LEFT OUTER JOIN
  projects
ON
  projects.autor = users.id
GROUP BY
  users.id
ORDER BY
  num_projects DESC

Upvotes: 1

Related Questions