Complex SQL Query For Counting and Listing

meursault 0
Viewed 47

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
oracle-sqldeveloper hibernate java sql
3 Answers

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

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;
userIBpt0

I think 'DISTINCT' will help you.

Example -

SELECT DISTINCT NAME, COUNT(ID) as ID_COUNT FROM TABLE GROUP BY NAME ORDER BY ID and use `asc` or `desc` as your requirement.

DISTINCT - will give you unique name COUNT(ID) - Will give you count of id's.

user165148210
Related