Issue
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
Solution
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")
Answered By - Chaosfire
Answer Checked By - Willingham (JavaFixing Volunteer)