Count distinct occurrences and add them-CodePudding

I have the following table, | Name | Choice | |---------------------|------------------| | John | 1 | | John | 1 | | John | 3 | | Alex | 3 | | John | 4 | | Liam | 2 |

I want to find how many distinct choices everyone has. So the output after the query should be, | Name | numberOfChoices | |---------------------|------------------| | John | 3 | | Alex | 1 | | Liam | 1 |

I've been trying to use GROUP BY and INNNER JOIN but cant seem to make this work. Any suggestions?

CodePudding user response：

GROUP BY name and count DISTINCT choices

SELECT `Name`, COUNT(DISTINCT `Choice`)
FROM mytable
GROUP BY `Name`