Display total amount of each products on the table

I have a table called prod_Table that I got from a SQL query:

SELECT PROD_ID, PROD_NAME, AMT, FROM ITEMS ORDER BY PROD_NAME;

I’m using itextpdf to display the table. I was able separate the table according to PROD_NAME:

APPLE
PROD_ID | AMT
11111     12.75
22222     13.75
33333     14.75

ORANGE
PROD_ID | AMT
44444     15.75
55555     16.75

but now I want to display below the total of each products. The output is something like this:

APPLE
PROD_ID | AMT
11111     12.75
22222     13.75
33333     14.75

TOTAL:    41.25 

ORANGE
PROD_ID | AMT
44444     15.75
55555     16.75

TOTAL:    32.5

This is the best that I could do and the output is not even right. I’m pretty much stuck.

List<String> prod_Names = new ArrayList<>();
int total = 0;
while(rs.next()){
    String prodName = rs.getString(2);
    
    if (!prod_Names.contains(prodName)){
        prod_Names.add(prodName);
            
        PdfPCell name = new PdfPCell(new Phrase(prodname, bold));
        name.setColspan(2);
        prod_Table.addCell(name);
            
        prod_Table.addCell(new Phrase("PROD_ID", header_Bold));
        prod_Table.addCell(new Phrase("AMT", header_Bold));
    }
        
    String prodId_Values = result.getInt(1);
    int amt_Values = result.getInt(3);
    total += amt_Values;
        
    //Display Values
    prod_Table.addCell(new Phrase(Integer.toString(prodId_Values), normalFont));
    prod_Table.addCell(new Phrase(Integer.toString(amt_Values), normalFont));
        
    // Display Total
    prod_Table.addCell(new Phrase("TOTAL:", normalFont));
    prod_Table.addCell(new Phrase(Integer.toString(total), normalFont));
}

The output of my code:

APPLE
PROD_ID | AMT
11111     12.75
TOTAL:    12.75

22222     13.75
TOTAL:    26.5
 
33333     14.75
TOTAL:    41.25 

ORANGE
PROD_ID | AMT
44444     15.75
TOTAL:    57.00

55555     16.75
TOTAL:    73.75

Answer

You can put the total on each row of the query resulste with a window sum:

select prod_id, prod_name, amt, sum(amt) over(partition by prod_name) as total_amt
from items 
order by prod_name;

Then all that is left to do is to arrange the application code so total_amt is displayed at the same level as prod_name. As I understand your question, you have that logic in place already.

Advertisement

Answer