Question

Consider the following code example:

SELECT VendorName, COUNT (*) AS NumberOfInvoices,
MAX (InvoiceTotal - PaymentTotal - CreditTotal) AS BalaneDue

FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID

WHERE InvoiceTotal - PaymentTotal - CreditTotal >
(SELECT AVG (InvoiceTotal - PaymentTotal- CreditTotal)
FROM Invoices)

GROUP BY VendorName
ORDER BY BalanceDue DESC;

When this query is executed, the NumberOfInvoices column for each row will show the number

Answer 1

The number of invoices for each vendor that have a larger balance due than the average balance due for all invoices.

Step-by-step explanation:

This part of the code below

WHERE InvoiceTotal - PaymentTotal - CreditTotal >

(SELECT AVG (InvoiceTotal - PaymentTotal- CreditTotal)

FROM Invoices)

gives the condition to pick the invoices with balance that are greater than the average balance for all invoice.

This part of the code below

GROUP BY VendorName

ORDER BY BalanceDue DESC;

then enables the program to group the result of the above condition by VendorName and sort the resulting rows in the order of BalanceDue. You will therefore, obtain for each row in the NumberOfInvoices column, the number of invoices for each vendor that have a larger balance due than the average balance due for all invoices.