COUNT(*) function is returning multiple values

I am writing a specific sql query that needs to return the position of a particular entry, based on a grouped table.

Background info: I am coding a Golf Club Data Management system using Java and MS Access. In this system, the user is able to store their scores as a new entry into this table. Using this table, I have managed to extract a ranking of the top 3 Golf players, using all their recorded scores (I only used top 3 to preserve screen space).

Select TOP 3 Username, Sum(Points) 
FROM Scores 
GROUP By Username 
ORDER BY Sum(Points) desc

This produces the required result. However, if the current user falls outside of the top 3, I want to be able to tell the user where they currently sit in the complete ranking of all the players. So, I tried to write a query that counts the number of players having a sum of points below the current user. Here is my query:

Select COUNT(*) 
From Scores
GROUP BY Username
HAVING Sum(Points) < (Select Sum(Points)
  FROM Scores
  WHERE Username = 'Golfer210'
  GROUP By Username)

This does not produce the expected number 2, but instead does this.

I have tried removing the GROUP BY function but that returns null. The COUNT DISTINCT Function refuses to work as well, and continuously returns a syntax error message, no matter how I word it.

Questions: Is there a way to count the number of entries while using a GROUP BY function? if not, is there an easier, more practical way to select the position of an entry from the grouped table? Or can this only be done in Java, after the ranking has been extracted from the database? I have not been able to find a solution anywhere

Answer

You need an additional level of aggregation:

SELECT COUNT(*)
FROM (SELECT COUNT(*) 
      FROM Scores
      GROUP BY Username
      HAVING Sum(Points) < (SELECT Sum(Points)
                            FROM Scores
                            WHERE Username = 'Golfer210'
                           )
     ) as s;

Note: You might want to check if your logic does what you expect when there are ties.

Advertisement

Answer