Question

Write a SELECT statement that returns two columns: vendor_id and the largest unpaid invoice for each vendor. To do this, you can group the result set by the vendor_id column. This should return 7 rows. Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return a single value that represents the sum of the largest unpaid invoices for each vendor\.\*/

Answer 1

1. SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax

FROM Invoices

WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0

GROUP BY VendorID

2. SELECT SUM(InvoiceMax) AS SumOfMaximums

FROM (SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax

FROM Invoices

WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0

GROUP BY VendorID) AS MaxInvoice