Complex SQL Query For Counting and Listing-CodePudding

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

CodePudding user response：

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")

CodePudding user response：

you can use group by for counting popularity and distinct for unique names

select distinct name, count(id) as pop from test group by name order by pop desc;

CodePudding user response：

I think 'DISTINCT' will help you.

Example -

SELECT DISTINCT NAME FROM TABLE