CODEWITHSUNDEEP
mysql
Danny
Danny

Reputation: 185

Adding sum from 2 different tables

I have something like this 2 tables: videos members

In the members table I have the name of each member:

1 Tom
2 Bob
3 Zack
4 Dan
5 Casey

In the videos table I have a column named members and I have the names in there seperated by commas

1. Tom,Dan
2. Casey,Zack,Bob
3. Tom,Casey,Dan,Zack
4. Zack,Bob,Dan

I'm trying to display how many times each member appears to get these results:

1 Tom = 2
2 Bob = 2
3 Zack = 3
4 Dan = 2
5 Casey = 2

Do I need to do something like SELECT SUM(members) WHERE and use LIKE?

Upvotes: 3

Views: 85

Answers (2)

1000111
1000111

Reputation: 13519

I would strongly suggest to normalize your data as others suggested.

Based on your current design you can use FIND_IN_SET to accomplish the result you want.

SELECT 
 M.id,
 M.name,
 COUNT(*) total
FROM members M 
INNER JOIN videos V ON FIND_IN_SET(M.name,V.members) > 0
GROUP BY M.name
ORDER BY M.id

See Demo

Running this query on your given data set you will get output like below:

| id |  name | total |
|----|-------|-------|
|  1 |   Tom |     2 |
|  2 |   Bob |     2 |
|  3 |  Zack |     3 |
|  4 |   Dan |     3 |
|  5 | Casey |     2 |

A must read

Is storing a delimited list in a database column really that bad?

More

This is how your vidoes table would look like if you normalize your data:

videos 

id   member_id

Upvotes: 3

Nir Levy
Nir Levy

Reputation: 12953

One way to go is to join the two tables, based on a like expression:

SELECT members.name, count (*) as counter from members inner join videos
ON videos.members like CONCAT('%,',members.name,',%')
GROUP BY members.name;

But I think the better solution will be like @e4c5 said in the comment - you need to normalize the data. the videos table should look like:

+---+-------+  
|ID | MEMBER|  
+---+-------+  
| 1 | Tom   |
| 1 | Dan   |
| 2 | Casey |
| 2 | Zack  |
| 2 | Bob   |
| 3 | Tom   |
| 3 | Casey |
| 3 | Dan   |
| 3 | Zack  |
| 4 | Zack  |
| 4 | Bob   |
| 4 | Dan   |
+---+-------+

That way, you can simply count on this table

Upvotes: 0

Related Questions