java - Get the last record in each group with sum/count

Question

There is a table table1 that contains data as shown below: code & amount is fields of table1.

I want to generate of report from records from table1 with group by each code and also get sum of each code, as shown below

Any idea about how to generate an output like above? For Java development

question from:https://stackoverflow.com/questions/65904165/get-the-last-record-in-each-group-with-sum-count

Answer 1

You're really looking for 2 sets of results here. The individual row output and the group by output require 2 separate queries.

You could

SELECT code, amount
FROM table1
ORDER BY code

for the individual amounts, and

SELECT code, sum(amount)
FROM table1
GROUP BY code

for the sums.

Personally I'd rather avoid making a second query. Just use the first query and do the summing/formatting from within my Java code.