MYSQL - Using SUM with JOIN

Question

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"

can u give some simple data?

echo_Me
– echo_Me

2013-02-08 13:27:04 +00:00
Commented Feb 8, 2013 at 13:27 — echo_Me
– echo_Me, Commented Feb 8, 2013 at 13:27

echo_Me · Accepted Answer · 2013-02-08 13:28:29Z

22

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

answered Feb 8, 2013 at 13:28

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

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

1 Comment

echo_Me Over a year ago

you welcome ! i wanted confirm by simple data if u gave me :) to show u on sqlfiddle demo. good luck !

Hamlet Hakobyan · Accepted Answer · 2013-02-08 13:27:02Z

1

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

answered Feb 8, 2013 at 13:27

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Comments

John Woo · Accepted Answer · 2013-02-08 13:28:15Z

1

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

answered Feb 8, 2013 at 13:28

John Woo

265k70 gold badges509 silver badges500 bronze badges

Collectives™ on Stack Overflow

MYSQL - Using SUM with JOIN

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related