0
I have the following problem:
I have a table with a list of values for a composite key. Following example:
ID | ItemCode | Localidade | Preco
---+----------+------------+-------
01 | 001 | 001 | 3.5
02 | 002 | 001 | 4.5
03 | 001 | 002 | 5.5
04 | 001 | 003 | 5.0
05 | 003 | 002 | 15.3
06 | 002 | 001 | 4.2
07 | 001 | 001 | 5.3
What I need is to "group" based on the combination of the columns Itemcode and Locality. But I need to use the Preco column to do a series of calculations. The point is that GROUP BY does not allow this listing, the Preco column should be included in an aggregation function such as SUM() or AVG(). How to do to list all prices for a particular item and location. Whereas I don’t know the values in any of the columns and need to use all the records, always selecting all the records for each Itemcode and Locality.
The return I wish would be so:
ItemCode | Localidade | Preco
---------+------------+-------
001 | 001 | [3.5, 5.3]
001 | 002 | [5.5]
001 | 003 | [5.0]
002 | 001 | [4.5, 4.2]
003 | 002 | [15.3]
I don’t know if it is possible to do this by JPA. I guess I’ll have to sort through the two columns and go through Resultset to mount the array.
Dear, how do you need this select to return the lines ? itemcode grouping and locality showing the prices on the same line ? or showing each price per line ?
– Lucas Fontes Gaspareto
@devgaspa I edited the question to explain better what I wish as a result.
– user34787
Same as your: http://stackoverflow.com/questions/24349598/how-to-aggregate-strings-with-jpa. might help with something.
– Lucas Fontes Gaspareto