CODEWITHSUNDEEP
mysqlsql
Cully
Cully

Reputation: 470

Getting a sum for every distinct value in a database

I have a database of Fantasy Football Managers and 15 players on each of their teams. I can do the following to list all the teams with "Robin Van Persie" in it.

SELECT * 
FROM `players` 
WHERE MATCH(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15) 
AGAINST ('Robin Van Persie' IN BOOLEAN MODE)

and I can do this to count how many have "Robin Van Persie" in it (returns just a number).

SELECT count(*) 
FROM `players` 
WHERE MATCH(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15) 
AGAINST ('Robin Van Persie' IN BOOLEAN MODE)

But is there a way for me to get a list similar to this?

Count | Player Name
-------------------------
62    | Robin Van Persie
12    | Wayne Rooney
22    | Michu
31    | Theo Walcott

For each distinct value in the database?

Upvotes: 2

Views: 148

Answers (1)

Dave
Dave

Reputation: 433

You really want to have three tables.

table 1
In the "players" table, you have 2 columns: the primary id, and the list of all the players.

table 2
The "managers" table also has 2 columns: each manager name, and a primary Id.

table 3
The "managers_players" table associates the primary ID of each manager with the primary id of each player on her or his team. So technically, this table has 3 columns: the table primary id, the managers id, and the players id. So this table will have multiple instances of both the same manager id and also the same player id.

With this structure, you can run a query like this (might need to use "binarys" on the inner joins):

SELECT m.name, count(mp.player_id) as count
FROM managers_players mp
inner join managers m
on  mp.manager_id =  m.id
inner join players p
on p.id = mp.player_id
group by m.id
order by count DESC;

This schema would also make it really easy to see the players on each managers team:

SELECT p.name
FROM managers_players mp
inner join managers m
on m.id = mp.manager_id
inner join players p
on p.id = mp.player id
where m.name like '%Manager Name%';

or do select m.name and change the where clause to p.name to see all the managers who have a particular player.

Upvotes: 1

Related Questions