MySQL Using SUM with multiple joins

Question

I have a projects table and a tasks table I want to do a query that gets all projects and the sum of the time_spent columns grouped by project id. So essentially list all projects and get the total of all the time_spent columns in the tasks table belonging to that project.

With the query posted below I get the latest added time_spent column and not the sum of all the columns.. :S

Below is the query I have at the moment:

SELECT `projects`.`id`, `projects`.`description`, `projects`.`created`,
`users`.`title`, `users`.`firstname`, `users`.`lastname`, `users2`.`title`
as assignee_title, `users2`.`firstname` as assignee_firstname,
`users2`.`lastname` as assignee_lastname,
(select sum(tasks2.time_spent)
from tasks tasks2
where tasks2.id = tasks.id)
as project_duration
FROM (`projects`)
LEFT JOIN `users`
ON `users`.`id` = `projects`.`user_id`
LEFT JOIN `users` as users2
ON `users2`.`id` = `projects`.`assignee_id`
LEFT JOIN `tasks` ON `tasks`.`project_id` = `projects`.`id`
GROUP BY `projects`.`id`
ORDER BY `projects`.`created` DESC

Below is my projects table:

Below is my tasks table:

Thanks in advance!

Answer 1

Usually this query will help you.

SELECT p.*, (SELECT SUM(t.time_spent) FROM tasks as t WHERE t.project_id = p.id) as project_fulltime FROM projects as p 

In your question, you don't say about users. Do you need users? You are on right way, maybe your JOINs can't fetch all data.