MySQL Error #1111

Question

I have a table of player performances and I want to return the high score for a each player, adding an asterisk ('*') after it if they were not out while achieving this score. This is the query I have built so far, but it gives me Error #1111: invalid use of the group function

 SELECT player_id, players.name,
 COUNT( * ) AS matches, 
 SUM( score ) AS runs,
(SELECT IF(is_out = 0, CONCAT(MAX(score),'*'), MAX(score)) FROM batting WHERE score = MAX(score) ) AS high
 FROM batting
 RIGHT JOIN players ON batting.player_id = players.p_id
 WHERE player_id <> 0
 GROUP BY player_id

I have looked at other times this error has come up and it supposedly indicates when a SUM function is performed over a MAX/MIN function, but I can't see where this is occurring in my query

Where am I going wrong and how would I rectify this, grma

Answer 1

Try this ie, add the column names(player_id, players.name) which you are selecting in your GROUP BY clause:

SELECT player_id, players.name,
 COUNT( * ) AS matches, 
 SUM( score ) AS runs,
(SELECT IF(is_out = 0, CONCAT(MAX(score),'*'), MAX(score)) FROM batting WHERE score = MAX(score) ) AS high
 FROM batting
 RIGHT JOIN players ON batting.player_id = players.p_id
 WHERE player_id <> 0
 GROUP BY player_id, players.name, high

Answer 2

SELECT IF(is_out = 0, CONCAT(MAX(score),'*'), MAX(score)) FROM batting WHERE score = MAX(score)

This doesn't make any sense (you can't use an aggregate function in the WHERE clause). Indeed I'm struggling to imagine what you expect it to do. MySQL is probably confused too.