CODEWITHSUNDEEP
mysqljoinsum
Jimmery
Jimmery

Reputation: 10119

MYSQL - Using SUM with JOIN

Ok, so I have 4 tables.

A users table with columns id and name.

A groups table with columns id, name and owner.

A items table with columns group and content.

A content table with columns id, name and duration.

Each user can have several groups. Each group can have several items inside it. Each item represents one of the pieces of content.

I want to be able to list all the groups, with a sum of all durations of each piece of content inside that group.

What Ive been trying is this:

select groups.id,groups.name,users.name,sum(content.duration) as duration from groups
join users on groups.owner=users.id
join items on items.group=groups.id
join content on content.id=items.content

Unfortunately this only gives me one result, with a total sum of all the durations of each piece of content in all of the groups - like so:

"g001", "Group 1", "Me", "400"

What I am expecting is something like:

"g001", "Group 1", "Me", "160"
"g002", "Group 2", "You", "160"
"g003", "Group 3", "Them", "80"

Upvotes: 15

Views: 38655

Answers (3)

echo_Me
echo_Me

Reputation: 37233

try this

   select groups.id,groups.name,users.name,sum(content.duration) as duration from groups
   join users on groups.owner=users.id
   join items on items.group=groups.id
   join content on content.id=items.content
   group by groups.name

Upvotes: 22

John Woo
John Woo

Reputation: 263693

LEFT JOIN allows you to display all groups even without content but the total value of the duration will be zero.

SELECT  a.id, a.name GroupName, 
        d.name OwnerName,
        SUM(c.duration) totals
FROM    groups a
        INNER JOIN users d
            ON a.owner = d.id
        LEFT JOIN items b
            ON a.id = b.group
        LEFT JOIN content c
            ON b.content = c.id
GROUP   BY a.id, a.name, d.name

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

select groups.id,groups.name,users.name,sum(content.duration) as duration
from groups
    join users
        on groups.owner=users.id
    join items
        on items.group=groups.id
    join content
        on content.id=items.content
group by groups.id,groups.name,users.name

Upvotes: 1

Related Questions