user889349 user889349 - 5 months ago 14
SQL Question

Mysql request from two tables with counting

I have two tables

**users**

id name
1 Name1
2 Name2

**tasks**

id user_id title
1 1 Task1
2 1 Task2
3 2 Task3


My request:

SELECT
U.*,
COUNT(T.id) AS tasks_total
FROM
`#__users` AS U
LEFT JOIN
`#__tasks` AS T
ON
U.id = T.user_id


I think something wrong here...

I wan't to get results:

id name tasks_total
1 Name1 2
2 Name2 1


Thanks!

Answer

You forgot to group the result by id

SELECT 
    U.*,
    COUNT(T.id) AS tasks_total 
FROM 
    `#__users` AS U
LEFT JOIN
    `#__tasks` AS T
ON
    U.id = T.user_id
group by U.id

demo on sqlfiddle

Comments