Group and reduce Elements

Question

My database is returning a DataSet like this

result

id  Percentage
1    20
1    15
2    30
2    40
3    10

that is mapped to a Contract entity

class Contract{
    private Integer id;
    private Double percentaje;
    ....
}

my expected result is having Map<Integer, String>

for example : 1, 20% 15%

the first value is the id, and the second is concat of the percentage

this is my approach so far

Map<Integer, String> res = result.stream().collect(Collectors.groupingBy(Contract::getId,
Collectors.reducing(0, Contract::getPercentage, (a, b) -> String.join("% ", a.toString(), b.toString()))
    );

intellij show me an error

Bad return type in lambda expression: String cannot be converted to Number & Comparable<? extends Number & Comparable<?>>

Any idea how to solve this, thanks

Answer 1

Your issue:

Bad return type in lambda expression: String cannot be converted to Number & Comparable>

might be with the Collectors.reducing call.

It should be:

Collectors.reducing(
   "",  // Contract to default String
   contract -> contract.getPercentage().toString() + "%",  // mapping Contract to String 
   (a, b) -> String.join(" ", a, b)  // reducing two ready-mapped Strings
)

compare to the declaration of Collectors.reducing:

public static <T,U> Collector<T,?,U> reducing(
   U identity,
   Function<? super T,? extends U> mapper,
   BinaryOperator<U> op
)

where U is your output type String, and T is your input type Contract.

Answer 2

Note that this could have been done directly from the Database as well, but this work for Java 8.

    List<Contract> result = new ArrayList<>();
    result.add(new Contract(1, 20));
    result.add(new Contract(1, 15));
    result.add(new Contract(2, 30));
    result.add(new Contract(2, 40));
    result.add(new Contract(3, 10));
        
    Map<Integer, String> mapped = new HashMap<>();
        
    result.stream().collect(Collectors.groupingBy(Contract::getId)).forEach((k, l) -> 
        mapped.put(k, l.stream().map(c -> c.getPercentaje().toString().concat("% ")).collect(Collectors.joining())
        )
    );
        
    mapped.forEach((k, v) -> System.out.println(k + ", " + v));

The result of Collectors.groupingBy is a Map<Integer, List<Contract>>. While it should be possible to do a 'one liner', I think is easier to understand by looping through the first map and add the result "reduction" to a different map.

Just for completness, the query for this result in MySQL is as follows

SELECT t1.id, GROUP_CONCAT(DISTINCT CONCAT(t2.percentaje, '%') SEPARATOR ' ') 
FROM table1 t1 
    JOIN table1 t2 ON t2.id = t1.id
GROUP BY t1.id;

GROUP_CONCAT is a pretty useful function for these case scenarios. You can read further in the documentation for it. (Thanks to hc_dev)

I think both of these approaches can be further optimized, specially the query.

Java fiddle here. SQL fiddle here.