CODEWITHSUNDEEP
sqljpajpql
Henrik
Henrik

Reputation: 1807

Convert SQL statement to JPQL statement

I wonder how I convert this SQL statement to JPQL:

SELECT sum(price) AS Income, count(*) AS Passages, Station.Name 
FROM Passage
INNER JOIN station ON passage.stationId = station.id
GROUP BY Station.Name

wouldnt it be something like this:

SELECT sum(price) AS Income, count(p) AS Passages, s.Name FROM Passage p
INNER JOIN station s     
ON p.stationId = s.id GROUP BY s.Name

?

Upvotes: 2

Views: 13622

Answers (2)

ajsanchez22
ajsanchez22

Reputation: 145

There is no ON in JPQL (JPA 2.0), but you can do an implicit join and use the WHERE clause:

Assuming that the entity Passage the atriburo StationID is the same type of object that the attribute id of the entity Station.

SELECT SUM(s.price) Income, COUNT(p) Passages, s.Name
FROM Passage p, Station s
WHERE p.stationId = s.id
GROUP BY s.Name

It is recommended that if the question can add entities Station Passage and to give a correct answer.

Upvotes: 4

Henrik
Henrik

Reputation: 1807

Problem solved with:

SELECT count(p), sum(p.price), s 
FROM Passage p 
INNER JOIN p.station s 
GROUP BY s.name;

Upvotes: 3

Related Questions