Group by multiple variables by shortest value and sum in Java

Question

I want to group by multiple variables and sum with number and get the result with list in java. Like SQL group by, I want to merge the data record with the lowest string. What I want to do is the same as the SQL below,

select orderId, itemId, itemName, itemGenre, sum(number) as number
from item
group by itemId, itemName, itemGenre;

If data exist in the item table below,

orderId(PK), itemId, itemName, itemGenre, number
00-82-947, 8810, item name1, 01, 1
00-82-952, 8810, item name1, 01, 2
00-91-135, 8315, item name2, 02, 3
00-91-140, 8315, item name3, 02, 4

I expected the result to be below. When grouping with the orderId by 00-82-947 and 00-82-952, I want to get the lower one like SQL group by.

00-82-947, 8810, item name1, 01, 3, 
00-91-135, 8315, item name2, 02, 3, 
00-91-140, 8315, item name3, 02, 4

How can I implement this in Java? I think this works for me but in this case orderId that is not grouped by will be null so I need to create a new class to fill the orderId. http://codestudyblog.com/questions/sf/0421195604.html

This also would work but I want the result with list. So I need to covert it to map three times as I need to group by three times. Group by multiple field names in java 8

So I'm looking for a better way probably using java stream. As a reference, I leave the code.

import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
public class Item {
    private String orderId;
    private String itemId;
    private String itemName;
    private String itemGenre;
    private Integer number;

}

Prepare data

   final ArrayList<Item> items = new ArrayList<>();
   items.add(new Item("00-82-947", "8810", "item name1", "01", 1));
   items.add(new Item("00-82-952", "8810", "item name1", "01", 2));
   items.add(new Item("00-91-135", "8315", "item name2", "02", 3));
   items.add(new Item("00-91-140", "8315", "item name3", "02", 4));
   
   System.out.println(items);

And I want print result to be below.

[Item(orderId=00-82-947, itemId=8810, itemName=item name1, itemGenre=01, number=3), 
Item(orderId=00-91-135, itemId=8315, itemName=item name2, itemGenre=02, number=3), 
Item(orderId=00-91-140, itemId=8315, itemName=item name3, itemGenre=02, number=4)]

Answer 1

You need to apply the min() aggregate function to the orderid as follows:

select min(orderId), itemId, itemName, itemGenre, sum(number) as number
from item
group by itemId, itemName, itemGenre;

And try this.

static String min(String a, String b) { return a.compareTo(b) <= 0 ? a : b; }

public static void main(String[] args) {

    record Item(String orderId, String itemId, String itemName, String itemGenre, Integer number) {}
    List<Item> items = List.of(
        new Item("00-82-947", "8810", "item name1", "01", 1),
        new Item("00-82-952", "8810", "item name1", "01", 2),
        new Item("00-91-135", "8315", "item name2", "02", 3),
        new Item("00-91-140", "8315", "item name3", "02", 4));

    record ItemKey(String itemId, String itemName, String itemGenre) {}
    record ItemValue(String orderId, Integer number) {}

    Map<ItemKey, ItemValue> map = items.stream()
        .collect(Collectors.toMap(
            e -> new ItemKey(e.itemId(), e.itemName(), e.itemGenre()),
            e -> new ItemValue(e.orderId(), e.number()),
            (a, b) -> new ItemValue(min(a.orderId(), b.orderId()), a.number() + b.number()),
            LinkedHashMap::new));
            
    for (Entry<ItemKey, ItemValue> e : map.entrySet())
        System.out.println(e);
}

output:

ItemKey[itemId=8810, itemName=item name1, itemGenre=01]=ItemValue[orderId=00-82-947, number=3]
ItemKey[itemId=8315, itemName=item name2, itemGenre=02]=ItemValue[orderId=00-91-135, number=3]
ItemKey[itemId=8315, itemName=item name3, itemGenre=02]=ItemValue[orderId=00-91-140, number=4]

Answer 2

The SQL query seems to be missing aggregation function MIN applied to orderId:

SELECT MIN(orderId), itemId, itemName, itemGenre, SUM(number) as number
FROM item
GROUP BY itemId, itemName, itemGenre;

To implement similar functionality using Stream API Collectors.toMap with merge function should to be used where merge function selects a min of orderId and sums up number. It may be also better to use LinkedHashMap to maintain insertion order.

Also, a copy constructor should be implemented in Item class or clone the items from items list when selecting a value to be placed to the intermediate map.

Then the values of this map are converted into ArrayList.

List<Item> summary = new ArrayList<>(items
        .stream()
        .collect(Collectors.toMap(
            // compound "group by" key using fields for brevity
            i -> String.join("|", i.itemId, i.itemName, i.itemGenre),
            i -> i.clone(), // or Item::new if copy constructor is implemented
                            // or verbose i -> new Item(i.orderId, i.itemId, ...)
            (i1, i2) -> {
                if (i1.orderId.compareToIgnoreCase(i2.orderId) < 0) {
                    i1.setOrderId(i2.orderId);
                }
                i1.setNumber(i1.number + i2.number);
                return i1;
            },
            LinkedHashMap::new
        ),
        )
        .values() // Collection<Item>
);

Or, a new object may be created in merge function:

List<Item> summary = new ArrayList<>(items
        .stream()
        .collect(Collectors.toMap(
            // compound "group by" key using fields for brevity
            i -> String.join("|", i.itemId, i.itemName, i.itemGenre),
            i -> i, // or Function.identity()
            (i1, i2) -> new Item( // merge function
                i1.orderId.compareToIgnoreCase(i2.orderId) <= 0 ? i1.orderId : i2.orderId,
                i1.itemId, i1.itemName, i1.itemGenre, // "group by" fields
                i1.number + i2.number
            ),
            LinkedHashMap::new
        ))
        .values() // Collection<Item>
);

Answer 3

I like to keep my stream code short and easy to overview even when it sometimes means having to hide some more code behind the scenes to get things to work. So my go is:

    List<Item> items = List.of(
            new Item("00-82-947", "8810", "item name1", "01", 1),
            new Item("00-82-952", "8810", "item name1", "01", 2),
            new Item("00-91-135", "8315", "item name2", "02", 3),
            new Item("00-91-140", "8315", "item name3", "02", 4));
    
    Map<GroupByKey, List<Item>> lists = items.stream()
            .collect(Collectors.groupingBy(Item::getGroupByKey));
    Map<GroupByKey, SumForOrder> grouped = lists.entrySet()
            .stream()
            .collect(Collectors.toMap(Map.Entry::getKey, e -> new SumForOrder(e.getValue())));
    
    grouped.forEach((k, v) -> System.out.println("" + k + " -> " + v));

Output:

8810 item name1 01 -> 00-82-947  3
8315 item name3 02 -> 00-91-140  4
8315 item name2 02 -> 00-91-135  3

I am first performing a usual groupingBy operation sorting your items into lists for each group. For this I have created a GroupByKey class containing itemId, itemName and itemGenre, and a method getGroupByKey of your Item class that constructs a GroupByKey object.

public GroupByKey getGroupByKey() {
    return new GroupByKey(itemId, itemName, itemGenre);
}

Next I am transforming the map of lists into a map containing objects of another class I created for the purpose, SumForOrder. The constructor of SumForOrder does much of the real work, from a list of items finding the minimum orderId and summing up the numbers:

public class SumForOrder {

    private String orderId;
    private int sum;

    public SumForOrder(Collection<Item> itemsForOrder) {
        orderId = itemsForOrder.stream()
                .map(Item::getOrderId)
                .min(Comparator.naturalOrder())
                .orElseThrow();
        sum = itemsForOrder.stream()
                .map(Item::getNumber)
                .filter(Objects::nonNull)
                .mapToInt(Integer::intValue)
                .sum();
    }

    @Override
    public String toString() {
        return String.format("%-9s %2d", orderId, sum);
    }

}

Instead of SumForOrder objects you may also simply create new Item objects. In this case you don’t need the SumForOrder class.