Adding Postgres table cells based on same value

I have a table called expenses. There are numerous columns but the ones involved in my php page are date, spplierinv, amount. I have created a page that lists all the expenses in a given month and totals it at the end. However, each row has a value, but many rows might be on the same supplier invoice.This means adding each row with the same supplierinv to get a total as per my bank statement. Is there anyway I can get a total for the rows based on the supplierinv. I mean say I have 10 rows. 5 on supplierinv 4, two on supplierinv 5 and 3 on开发者_JAVA技巧 supplierinv 12, how can a get 3 figures (inv 4, 5 and 12) and the grand total at the bottom. Many thanks

Do you need to show the individual expense rows as well as all the totals? If not, you can group by the supplierinv column and sum the amounts:

select supplierinv, sum(amount) as invoice_amount
from expenses group by supplierinv

then you can simply add all the invoice_amount values client-side to get the grand total.

if you do need each expense row, you can do the invoice aggregation client-side fairly easily:

$invtotals = array();
$grand_total = 0;
foreach (getexpenses() as $row) {
    $supplierinv = $row['supplierinv'];
    if (!array_key_exists($supplierinv, $invtotals)) {
        $invtotals[$supplierinv] = 0;
    }
    $invtotals[$supplierinv] += $row['amount'];
    $grand_total += $row['amount'];
}

another approach, if you want to show the table split up into invoices, is to just ensure the results are ordered by supplierinv and then:

$grand_total = 0;
$invoice_total = 0;
$current_invoice = -1;
foreach (getexpenses() as $row) {
    if ($current_invoice > 0 && $current_invoice != $row['supplierinv']) {
        show_invoice_total($current_invoice, $invoice_total);
        $grand_total += $invoice_amount;
        $current_invoice = $row['supplierinv'];
        $invoice_total = 0;
    }
    $invoice_total += $row['amount'];
    show_expense($row);
}
if ($current_invoice > 0) {
    show_invoice_total($current_invoice, $invoice_total);
    $grand_total += $invoice_amount;
}
show_grand_total($grand_total);

You can have the database do the aggregation for each invoice and the grand total like so:

select date, supplierinv, amount,
       sum(amount) over(partition by supplierinv) as invoice_amount,
       sum(amount) over()
from expenses

although you still have to do some post-processing to display this sensibly, so this doesn't really gain anything imho.