MySQL count amount of values and then return top 3 of them-CodePudding

Below is a table from which I want to make a query from:

 ------- ----------- 
| id    |color_value|     
 ------- ----------- 
| 3     | yellow    |    
| 5     | yellow    |   
| 12    | red       |   
| 33    | green     |    
| 47    | purple    |   
| 58    | red       |  
| 59    | red       |    
| 61    | orange    |   
| 62    | green     |

So what I want to achieve is to first count the total of color values for each color and then return top 3 used colors.

CodePudding user response：

For the first one (return count of Colors)

SELECT color_value, COUNT(color_value) FROM db GROUP BY color_value

For the second one (return 3 most used colors) :

SELECT color_value FROM db GROUP BY color_value ORDER BY 
COUNT(color_value) DESC LIMIT 3