Display the total amount of each products below the table

I’m using itextpdf to display the sql query

SELECT prod_id, prod_name, amt, sum(amt) over(partition by prod_name) as total_amt
FROM items 
ORDER BY prod_name;

I was able separate the table according to PROD_NAME, and calculate the total amount of each products by getting the sum(amt) over(partition by prod_name) as total_amt from the sql query. Now, I’ve been trying to display the total amount of each products below 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

However, this is the output of my code. The Total amount is displayed after each row.

APPLE
PROD_ID | AMT
11111     12.75
Total:    41.25
22222     13.75
Total:    41.25
33333     14.75
Total:    41.25

ORANGE
PROD_ID | AMT
44444     15.75
Total:    32.5
55555     16.75
Total:    32.5

Here is the snippet of my code:

List<String> prod_Names = new ArrayList<>();
 
while(rs.next()){
    String prodName = rs.getString(2);
    
    if (!prod_Names.contains(prodName)){
        prod_Names.add(prodName);
        
        // Displays the Product Name on top of the table
        PdfPCell name = new PdfPCell(new Phrase(prodname, bold));
        name.setColspan(2);
        prod_Table.addCell(name);
       
        // Displays the Row Header     
        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);  // amount
    int totalAmt = result.getInt(4);    // total amount of each products
    
        
    //Displays the  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(totalAmt), normalFont));
    
}

I tried putting the Display Total lines inside an if condition just like how I did with the Product Name. I also added another ArrayList called prod_Names2.

if(!prod_Names2.contains(prodName)){
 prod_Names2.add(prodName);

 // Display Total
 prod_Table.addCell(new Phrase("TOTAL:", normalFont));
 prod_Table.addCell(new Phrase(Integer.toString(totalAmt), normalFont));

}

The total amount is now only displayed one time, but it’s displayed after one row of each products like this. This is the best that I could do:

APPLE
PROD_ID | AMT
11111     12.75
Total:    41.25
22222     13.75
33333     14.75


ORANGE
PROD_ID | AMT
44444     15.75
Total:    32.5
55555     16.75

Answer

In Standard SQL you can do what you want using grouping sets:

SELECT prod_id, prod_name, SUM(amt) as amt
FROM items 
GROUP BY GROUPING SETS ( (prod_id, prod_name), () )
ORDER BY prod_name NULLS LAST;

Not all databases support exactly this syntax, but most support some variation of it. Then your java code can just read the results from the query.

Advertisement

Answer