query that add summary rows on invoice table (summary by single invoice )

Hi and thanks in advance to who will answer the following question: is it possible to have a SQL query that add 2 summary rows on invoice table, at the single invoice level? 1st row to add = discount (that is a column) 2nd row to add = total (price – discount)

Table example:

Desired Result:

Answer

You could use a union approach here:

SELECT InvoiceNo, date, price AS Summary, 1 AS pos FROM yourTable UNION ALL
SELECT InvoiceNo, date, discount, 2 FROM yourTable UNION ALL
SELECT InvoiceNo, date, price - discount, 3 FROM yourTable
ORDER BY InvoiceNo, pos;

Note carefully the introduction of the computed column aliased as pos. This places price records before discount records, which in turn are placed before the record having the difference.