How do I create a new total row for each invoice that shows the sum of each unique invoice?

Question

How do I create a new total row for each invoice that shows the sum of each unique invoice? I believe that there is a window function using a partition by. In the picture below, each invoice should have the same 'TAX AMT' and 'FRGHT AMT' if any (in this case it happens to be 0.00). I'm trying to create a new row after each 'INVOICE' that sums the 'TAX AMT', 'FRGHT AMT' one time and 'EXT PRICE' and places it in 'TOT MAT'.

The second image shows what I'm trying to produce.

SELECT ORDERNUM AS INVOICE
    ,PRODUCTLIST.ID AS ITEM
    ,PRODUCTLIST.DESC1 AS [DESC]
    ,INVDATE AS [INV DATE]
    ,format(USELL, 'N') AS PRICE
    ,QTYSOLD - QTYRETN AS QTY
    ,format(DOLSOLD - DOLRETN, 'N') AS [EXT PRICE]
    ,format(TAX_AMOUNT, 'N') AS [TAX AMT]
    ,format(FREIGHT, 'N') AS [FRGHT AMT]
    ,0.00 AS [HDL]
    ,0.00 AS [TOT MAT]
    ,PO_NUMBER AS [PO NBR]
FROM History
INNER JOIN PRODUCTLIST ON PARTNUM = UID
WHERE BILLTOID = '28736'
    AND INVDATE >= '2/21/2020' --and ordernum = 'S2149853.001'
ORDER BY INVDATE
    ,ORDERNUM

Answer 1

Edit 01: Updated final query to not sum TAXAMT and FRGHTAMT

You were on the right track. We'll use windowed aggregations to get the total by invoice and a union to add it as a new line.

Creating sample data:

DECLARE @tbl TABLE (
    INVOICE INT,
    EXTPRICE NUMERIC(32,2),
    TAXAMT NUMERIC(32,2),
    FRGHTAMT NUMERIC(32,2)
)

INSERT INTO @tbl
VALUES
(1,172.5,0,0)
,(1,105.26,0,0)
,(1,15.76,0,0)
,(1,129.64,0,0)
,(1,42.1,0,0)
,(1,284.22,0,0)
,(1,23.14,0,0)
,(1,90.52,0,0)
,(1,168.4,0,0)
,(2,11.46,0,0)
,(2,13.75,0,0)
,(2,17.95,0,0)
,(2,13.46,0,0)
,(2,13.46,0,0)

Next we need to calculate the total EXTPRICE by invoice + total TAXAMT by invoice + total FRGHTAMT by invoice.

SELECT
    *,
    SUM(EXTPRICE) OVER (PARTITION BY INVOICE)   --Total EXTPRICE by invoice
    + SUM(TAXAMT) OVER (PARTITION BY INVOICE)   --Total TAXAMT by invoice
    + SUM(FRGHTAMT) OVER (PARTITION BY INVOICE) --TOTAL FRGHTAMT by invoice
        AS InvoiceTotal
FROM @tbl

Finally, for this to be a new line instead of an additional column, we'll need to query the table twice and union them. Because unions require the same number of columns and data types, I am leaving the Invoice Total as NULL for the line items and leaving the individual prices NULL for the total. You could also do a running total by line item if you wanted.

Edit 01: InvoiceTotal updated to only count TAXAMT & FRGHTAMT once instead of summing it in a windowed function.

SELECT
    INVOICE,
    'LineItem' AS RecordType,
    EXTPRICE,
    TAXAMT,
    FRGHTAMT,
    NULL AS InvoiceTotal
FROM @tbl
UNION ALL
SELECT DISTINCT
    INVOICE,
    'Total',
    NULL,
    NULL,
    NULL,
    SUM(EXTPRICE) OVER (PARTITION BY INVOICE)   --Total EXTPRICE by invoice
    + TAXAMT
    + FRGHTAMT
        AS InvoiceTotal
FROM @tbl
ORDER BY INVOICE,RecordType