In my problem, I want to show the id and this particular id related key-value pair (x: date, y: sum of total price). But I don’t get this result.
I am using sum, concat and group concat in criteriabuilder. But an error came that “Invalid use of group function”. And I want that it will return the sum of every date present in database. Like there is only one data at a date then show the one if not then sum of all values.
I am trying this,
Expression<String> grnCreatedAt = root.get("grnCreatedAt");
Expression<BigDecimal> itemPriceSum = criteriaBuilder.sum(grnItemJoin.get("totalAmount"));
Expression<String> itemPriceSumAsString = itemPriceSum.as(String.class);
// Construct the JSON structure
Expression<String> dataJson = criteriaBuilder.concat(criteriaBuilder.literal("{"x": ""),
criteriaBuilder.concat(grnCreatedAt, criteriaBuilder.literal("", "y": ")));
dataJson = criteriaBuilder.concat(dataJson,
criteriaBuilder.concat(itemPriceSumAsString, criteriaBuilder.literal("}")));
Expression<String> groupConcat = criteriaBuilder.function("GROUP_CONCAT", String.class, dataJson);
Expression<String> data = criteriaBuilder.concat(criteriaBuilder.literal("["),
criteriaBuilder.concat(groupConcat, criteriaBuilder.literal("]")));
if (bumpRequest.getType().equalsIgnoreCase("grn")) {
query.multiselect(grnStorageName.alias("grnStorageName"), data.alias("data"),
itemPriceSumAsString.alias("item_price"));
} else {
query.multiselect(grnCostCenterDesc.alias("grnCostCenterDesc"), data.alias("data"),
itemPriceSumAsString.alias("item_price"));}
query.where(criteriaBuilder.and(companyPredicate, branchPredicate, locationPredicate, datePredicate,
grnTypePredicate));
if (bumpRequest.getType.equalsIgnoreCase("grn")) {
query.groupBy(groupByField, grnStorageName);
} else {
query.groupBy(groupByField, grnCostCenterDesc);
}
query.orderBy(criteriaBuilder.desc(itemPriceSumAsString));
Error: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [select gs1_0.storage_location_name,concat('[',concat(GROUP_CONCAT(concat(concat('{"x": "',concat(g1_0.grnCreatedAt,'", "y": ')),concat(cast(sum(i1_0.totalAmount) as char),'}'))),']')),cast(sum(i1_0.totalAmount) as char) from erp_grn g1_0 join erp_grn_goods i1_0 on g1_0.grnId=i1_0.grnId join erp_storage_location gs1_0 on gs1_0.storage_location_id=g1_0.functional_area where g1_0.companyId=? and g1_0.branchId=? and g1_0.locationId=? and g1_0.grnCreatedAt between ? and ? and g1_0.grnType=? group by gs1_0.storage_location_code,1 order by 3 desc limit ?] [Invalid use of group function] [n/a]
2