CODEWITHSUNDEEP
mysqlspringhibernatejpa
Carlos Devesa
Carlos Devesa

Reputation: 97

Can I sum values in a order by in JPA?

I'm developing a Spring Boot App and I use JPA. My question is:

I have 3 fields (points1, points2 and points3) and I'm doing a ranking. So, I want to get the top of this ranking as a sum of these 3 fields.

Example to understand: 

SELECT u FROM User u ORDER BY u.totalPoints DESC;
Where u.totalPoints is the sum of points1, points2 and points3.

How can I do it? Thanks

Upvotes: 1

Views: 1172

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can defined aliases for expressions in the SELECT clause, and utilize those aliases in the ORDER BY clause:

SELECT *, (points1 + points2 + points3) AS total_points 
FROM user 
ORDER BY total_points DESC

Upvotes: 1

Maciej Kowalski
Maciej Kowalski

Reputation: 26522

You need a correlated query here:

SELECT u FROM User u 
Where u.totalPoints = (select uu.points1 + uu.points2 + uu.points3 
                       from User uu 
                       where uu.id = u.id)
ORDER BY u.totalPoints DESC;

Upvotes: 0

Related Questions