Question

Write a SELECT statement that returns six columns: VendorID From the Invoices table InvoiceDate From the Invoices table InvoiceTotal From the Invoices table VendorTotal The sum of the invoice totals for each vendor VendorCount The count of invoices for each vendor VendorAvg The average of the invoice totals for each vendor The result set should include the individual invoices for each vendor.

Answer 1

SELECT VendorID, InvoiceDate, InvoiceTotal, SUM(InvoiceTotal) OVER (PARTITION BY VendorID) AS VendorTotal, COUNT(InvoiceTotal) OVER (PARTITION BY VendorID) AS VendorCount, AVG(InvoiceTotal) OVER (PARTITION BY VendorID) AS VendorAvg FROM Invoices;

Step-by-step explanation:

The select query to invoices table is to return six column;

First column is VendorID

Second column is InvoiceDate

Third column is InvoiceTotal

Fourth is the sum of sum of the invoice totals for each vendor represented as VendorTotal

Fifth is the count of invoices for each vendor represented as VendorCount

Sixth is the average of the invoice totals for each vendor represented as VendorAvg

The result set should include the individual invoices for each vendor is the reason why we use VendorID in the query.