Complex SQL Query For Counting and Listing

I need to write complex sql for counting distinct names popularity and sorting them with descending order. I’ll do this with hibernate @Query(“SELECT * FROM …”) parameter. First I tried to do it on SQL then implement it to my spring boot project but I’m stuck while writing it.

SELECT
    f.Name,
    COUNT(SELECT * FROM Family g WHERE g.Name WHERE ???) AS numberOfCount

FROM Family AS f
ORDER BY numberOfCount

id  | Name     | Surname | 
--------------------------
1   | John     | Smith   | 
2   | Mary     | Smith   | 
3   | John     | Dawson  | 
4   | Lisa     | Smith   | 
5   | Lisa     | Dawson  | 
6   | Jack     | Smith   |
7   | John     | Smith   | 

Sorted version:

 Name    | Popularity
--------------------------
| John   |  3
| Lisa   |  2
| Mary   |  1
| Jack   |  1

Answer

I think you are looking for group by. The query will look like this:

SELECT name, COUNT(name) AS popularity 
FROM test 
GROUP BY NAME 
ORDER BY popularity DESC

The jpql should be like this:

@Query("SELECT t.name AS name, COUNT(t.name) AS popularity FROM Test t GROUP BY t.name ORDER BY popularity DESC")