CODEWITHSUNDEEP
mysql
Java
Java

Reputation: 2489

find out count of comma based value in MySql

I have two tables. 

Table Emp

    id   name
    1    Ajay
    2    Amol 
    3    Sanjay 
    4    Vijay

Table Sports

Sport_name     Played by
Cricket        ^2^,^3^,^4^
Football       ^1^,^3^ 
Vollyball      ^4^,^1^

Now I want to write a query which will give me output like

name   No_of_sports_played 
Ajay          2
Amol          1
Sanjay        2 
Vijay         2

So what will be Mysql query for this?

Upvotes: 0

Views: 272

Answers (2)

mathematical.coffee
mathematical.coffee

Reputation: 56915

I agree with the above answers/comments that you are not using a database for what a database is for, but here is how you could calculate your table from your current structure in case you have no control over that:

SELECT Emp.name, IF(Played_by IS NULL,0,COUNT(*)) as Num_Sports
FROM Emp
LEFT JOIN Sports
ON Sports.Played_by RLIKE CONCAT('[[:<:]]',Emp.id,'[[:>:]]')
GROUP BY Emp.name;

See it in action here. UPDATE: added the IF(Played_by IS NULL,0,COUNT(*)) instead of COUNT(*). This means that if an employee doesn't play anything they'll have a 0 as their Num_Sports. See it here (I also added in those ^ characters and it still works.

What it does is joins the Emp table to the Sports table if it can find the Emp.id in the corresponding Played_by column.

For example, if we wanted to see what sports Ajay played (id=1), we could do:

SELECT *
FROM Emp, Sports
WHERE Sports.Played_by LIKE '%1%'
  AND Emp.id=1;

The query I gave as my solution is basically the query above, with a GROUP BY Emp.name to perform it for each employee.

The one modification is the use of RLIKE instead of LIKE.

I use RLIKE '[[:<:]]employeeid[[:>:]]' instead of LIKE '%employeeid%. The [[:<:]] symbols just mean "make sure the employeeid you match is a whole word".

This prevents (e.g.) Emp.id 1 matching the 1 in the Played_by of 3,4,11,2.

Upvotes: 1

davidethell
davidethell

Reputation: 12018

You do not want to store your relationships in a column like that. Create this table:

CREATE TABLE player_sports (player_id INTEGER NOT NULL, sport_id INTEGER NOT NULL, PRIMARY KEY(player_id, sport_id));

This assumes you have an id column in your sports table. So now a player will have one record in player_sports for each sport they play.

Your final query will be:

SELECT p.name, COUNT(ps.player_id)
FROM players p, player_sports ps
WHERE ps.player_id = p.id
GROUP BY p.name;

Upvotes: 1

Related Questions