Question

1. Write a select statement that returns the vendorname and paymentsum of each vendor, where paymentsum is the sum of the paymentotal column. Return only the top ten vendors who have been paid the most and the number of invoices is >5.

CREATE TABLE InvoiceArchive InvoiceID int NOT NULL, VendorID int NOT NULL InvoiceNumber varchar(50) NOT NULL, InvoiceDate smalldatetime NOT NULL, Invoice Total money NOT NULL, Payment Total money NOT NULL, CreditTotal money NOT NULL, TermsID int NOT NULL, InvoiceDueDate smalldatetime NOT NULL, PaymentDate smalldatetime NULL /****** Object: Table InvoiceLineItems ****** CREATE TABLE InvoiceLineItems InvoiceID int NOT NULL InvoiceSequence smallint NOT NULL, Account No int NOT NULL, InvoiceLineItemAmount money NOT NULL, InvoiceLineItemDescription varchar(180) NOT NULL, CONSTRAINT PK_InvoiceLineItems PRIMARY KEY CLUSTERED InvoiceID ASC InvoiceSequence ASC /****** Object: Table Invoices E CREATE TABLE Invoices InvoiceID int IDENTITY(1,1) NOT NULL, VendorID int NOT NULL, InvoiceNumber varchar(50) NOT NULL, InvoiceDate smalldatetime NOT NULL, Invoice Total money NOT NULL, Payment Total money NOT NULL, CreditTotal money NOT NULL, TermsID int NOT NULL, InvoiceDueDate smalldatetime NOT NULL PaymentDate smalldatetime NULL,

Answer 1

If you have any doubts/errors, please give me comment...

SELECT TOP 10 VendorName, SUM(PaymentTotal) AS Payments

FROM Vendors V, Invoices I

WHERE V.VendorID = I.VendorID

GROUP BY V.VendorID, VendorName

HAVING COUNT(*)>5;