Adding Postgres table cells based on same value

Question

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 supplierinv 12, how can a get 3 figures (inv 4, 5 and 12) and the grand total at the bottom. Many thanks

Answer 1

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.