Adding sum from 2 different tables

Question

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?

what you need to do is normalize your data – e4c5 Sep 27 '16 at 06:32 — e4c5, Sep 27 '16 at 06:32
Double check your expected result. – 1000111 Sep 27 '16 at 06:51 — 1000111, Sep 27 '16 at 06:51

score 3 · Accepted Answer · edited May 23 '17 at 10:27

3

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

edited May 23 '17 at 10:27

Community

1
1

answered Sep 27 '16 at 06:53

1000111

13,169
2
28
37

I started to normalize my data. So now I have 3 tables videos, members, video_member. On video member I have 2 columns: `video_id`, `member_id` using the names above my columns would be filled like so: under `video_id` `1` `1` and next to those under `member_id` `1` `4` these two would be foreign keys and signify that `video_id 1` has members Tom and Dan. In my PHP project, I query the `videos` table data and, by using Twig Template Engine, I use a for loop to go through each row and output the `videos` table data. Creating a thumbnail video gallery.However I also want to display the members – Dan Sep 28 '16 at 06:32
Do you want to show the same output as above from your normalized database design? – 1000111 Sep 28 '16 at 06:34
Eventually, but removing my setup of having name,name,name with the commas broke the way I displayed data. I want to be able to display The members that belong to each video. Before I just got the string 'name,name,name' and split it into an array then each name was displayed under the video thumbnail with their own link – Dan Sep 28 '16 at 06:40
Would you mind posting this as a separate question? It's really tough answering through comments – 1000111 Sep 28 '16 at 06:41

score 0 · Answer 2 · answered Sep 27 '16 at 06:40

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

Adding sum from 2 different tables

2 Answers2

Linked