Display total where row number is max row_number

I have a query that displays invoice information, and in final column displays the Supplier’s total balance, I only want the total to be displayed on the last line of that supplier’s invoice entries.

ie. the results may contain 100 invoices for say 20 suppliers, each supplier having a different number of invoices, the Account_Total should only be displayed on the final row for that particular pt_account. (row_number is the MAX value for that pt_account.)

When I attempted, I got a windowed error.

select 
    row_number() over (order by pt_account, pt_trdate), 
    pt_account, pt_trdate, pt_supref, pt_trref, pt_trtype, 
    pt_trvalue - pt_vatval [Net], pt_vatval [Vat], PT_TRVALUE [Total], 
    pt_trbal [Balance],
    Account_Total = (select sum(pt.pt_trbal) from ptran pt 
                     where pt.pt_account = pt1.pt_account)
from 
    ptran pt1
where 
    pt_trbal <> 0
    and pt_advance <> 'Y'
    and pt_account like 'A%'

Answer

You can use a case expression. In addition, a subquery is not needed. You can use window functions:

select row_number() over (order by pt_account, pt_trdate),
       pt_account, pt_trdate, pt_supref, pt_trref, pt_trtype, pt_trvalue-pt_vatval [Net], pt_vatval [Vat], PT_TRVALUE [Total], pt_trbal [Balance],
       (case when 1 = row_number() over (order by pt_account, pt_trdate desc)
             then sum(pt.pt_trval) over (partition by pt_account)
        end) as Account_Total
from ptran pt1
where pt_trbal <> 0 and pt_advance <> 'Y' and pt_account like 'A%'
order by pt.account, pt_trdate;