MySQL Using SUM with multiple joins

Question

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

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

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

projects table: enter image description here

tasks table: enter image description here

Please ask 1 specific researched non-duplicate question. Either ask re 1 bad query/function with obligatory minimal reproducible example, including why you think it should return something else or are unsure at the 1st subexpression where you don't get what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a minimal reproducible example--then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. How to Ask Help center Basic questions are faqs. — philipxy
– philipxy, Commented May 20 at 14:28

philipxy · Accepted Answer · 2025-05-20 15:07:46Z

1

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

Maybe your JOINs don't fetch all the data you need, like users.

edited May 20 at 15:07

philipxy

15.2k6 gold badges44 silver badges101 bronze badges

answered Apr 24, 2015 at 13:11

Kristiyan

1,66314 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Over a year ago

I didn't post about users as it didn't matter I appended your SUM SQL to what I already have and it worked! Thanks!

Jonathan Over a year ago

they are all left joins so they will have no effect on projects and tasks since there's nothing in the where statement limiting the output based on the left joins.

Jonathan · Accepted Answer · 2015-04-24 13:16:29Z

This query should do it for you.

Note, whenever you do a group by you must include every column that you select from or order by. Some MySql installations don't prevent you from doing this, but in the end it results in an incorrect result set.

As well you should never do a query as part of your SELECT statement, known as a sub-query, as it will result in an equal amount of additional queries in relation to the number of rows returned. So if you got 1,000 rows back, it would result in 1,001 queries instead of 1 query.

SELECT 
    p.id,
    p.description,
    p.created,
    u.title,
    u.firstname,
    u.lastname,
    a.title assignee_title,
    a.firstname assignee_firstname,
    a.lastname assignee_lastname,
    SUM(t.time_spent) project_duration
FROM
    projects p

LEFT JOIN
    users u ON
        u.id = p.user_id
LEFT JOIN
    users a ON
        a.id = u.assignee_id
LEFT JOIN
    tasks t ON
        t.project_id = p.id
GROUP BY 
    p.id,
    p.description,
    p.created,
    u.title,
    u.firstname,
    u.lastname,
    a.title,
    a.firstname,
    a.lastname
ORDER BY
    p.created DESC

Collectives™ on Stack Overflow

MySQL Using SUM with multiple joins

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related