CODEWITHSUNDEEP
phpmysqlsqlcodeigniter-2
Richard
Richard

Reputation: 289

MySQL Using SUM with multiple joins

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: enter image description here

Below is my tasks table: enter image description here

Thanks in advance!

Upvotes: 1

Views: 119

Answers (2)

Kristiyan
Kristiyan

Reputation: 1663

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.

Upvotes: 1

Jonathan
Jonathan

Reputation: 2877

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

Upvotes: 1

Related Questions