I have trouble exactly explaining what my problem is. Let me start with what I am NOT asking: I am NOT asking for the minimal value of a column.

Assume the following table, which in one column lists names and in the other column lists guesses estimating the age of the person on the left. Multiple people are guessing so there are different guesses:

and so on and so on. My question is: What is an SQL command that could give me a table filled by all names who were guessed the LEAST to be for example 36 (must be a specific value !). Additionally I'd like also like to know how often they were guessed 36. If nobody guessed them 36 I'd like to know that too.

In this example only Jordan was guessed to be 36. All the others were never guessed to be 36. I would expect an output like this:

The table above is the result of me asking which people were guessed to be 36 the least amount of times.

My attempt was to group them by how often they were guessed 36. However if they were never rated 36, they also do not appear in the table at all, meaning I cannot just compute the minimum of the column.

CodePudding user response：

If you want to get count of guessed ages by each user, group by user

SELECT name, COUNT(ageGuess) AS total, ageGuess FROM user_guess GROUP BY name, ageGuess ORDER BY name, total ASC

You will get then something similar to this:

Please note that it will not return not guessed values. You can fill-in it when processing in back-end.

To have your wanted output, do it with sub-query:

SELECT name, (SELECT COUNT(ageGuess) FROM guesses g2 WHERE g2.name = g1.name AND ageGuess = 36) FROM guesses g1 GROUP BY name

Example