mySQL count occurances of a string

Question

I have this query...

SELECT SUM(brownlow_votes) AS votes, 
player_id, 
player_name, 
player_team, 
COUNT(*) AS vote_count 
FROM afl_brownlow_phantom, afl_playerstats 
WHERE player_id=brownlow_player 
AND brownlow_match=player_match 
GROUP BY player_id 
ORDER BY votes DESC LIMIT 50

So "votes" becomes the number of votes a player has, "vote_count" becomes the number of times (matches in which) a player has been voted for. This works fine.

However, I have another column called "brownlow_lock" which is either blank, or 'Y'. How do I get the number of occurances of 'Y'? I know I could solve this changing it to 0 or 1 and just doing a SUM() but I don't want to have to go and edit the tons of pages that are inserting data.

Martin Smith · Accepted Answer · 2011-03-28 21:02:45Z

1

If I have understood you correctly you just need to add

COUNT(CASE WHEN brownlow_lock='Y' THEN 1 END) AS Cnt

to your query

answered Mar 28, 2011 at 21:02

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

4 Comments

Mark Over a year ago

Thanks Martin, that only counts if there is a 'Y' for that player though? Sometimes they have more than 1...

Mark Over a year ago

Actually, I might be wrong here, I think I have a mistake in my query, it's not actually counting all the votes. I'll come back to this!

Martin Smith Over a year ago

@Mark - If there is more than one Y within a group they will be counted. Is that the desired result?

RichardTheKiwi Over a year ago

The "1" in there is for counting, not the result. It will give you what you are after

fredrik · Accepted Answer · 2011-03-28 21:10:49Z

0

Try using the IF control flow function

SELECT SUM(IF(brownlow_lock='Y',1,0)) lock_count ...

answered Mar 28, 2011 at 21:10

fredrik

13.6k4 gold badges37 silver badges54 bronze badges

1 Comment

Mark Over a year ago

Hi Fredrik, I tried that but it didnt work right. The answer above works well though. Thanks anyway :)

Collectives™ on Stack Overflow

mySQL count occurances of a string

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related