Sql – Convert SQL statement to JPQL statement

jpajpqlsql

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

?

Best Solution

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.