Question

Write a SELECT statement that returns the sum of the largest unpaid invoices submitted by each vendor. Use a derived table that returns MAX(InvoiceTotal) grouped by VendorID, filtering for invoices with a balance due.

TABLES:

Vendor

Columns: VendorID, VendorName, DefaultTermsID, DefaultAccountNo

Invoices

Columns: InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, CreditTotal, TermsID

Answer 1

Creating Vendor Table :

CREATE TABLE VENDOR (VENDORID INT PRIMARY KEY,VENDORNAME CHAR(20),DefaultTermsID char(20),DefaultAccountNo char(30));

Creating Invoices Table:

CREATE TABLE INVOICES (INVOICEID INT,VENDORID INT,INVOICENUMBER INT,INVOICEDATE DATE,INVOICETOTAL INT,PAYMENTTOTAL INT,CREDITTOTAL INT);

Adding Foreign Keys:

ALTER TABLE INVOICES ADD FOREIGN KEY (VENDORID) REFERENCES VENDOR(VENDORID);

Inserting values in Vendor Table:

INSERT INTO VENDOR VALUES (1,'Roman','SA1','786757890'),(2,'Adam','SA4','765431291'),(3,'Ding','SA5','976530189');

Inserting values in Invoices Table:

INSERT INTO INVOICES VALUES (100,1,1000,'2019-08-27',10000,8000,8000);
INSERT INTO INVOICES VALUES (100,1,1001,'2019-08-29',1000,8000,8000);
INSERT INTO INVOICES VALUES (100,1,1001,'2019-09-19',3000,8000,8000);
INSERT INTO INVOICES VALUES (101,2,1000,'2019-09-17',1000,100,2000);
INSERT INTO INVOICES VALUES (101,2,1001,'2019-09-18',2000,100,1000);
INSERT INTO INVOICES VALUES (102,3,1009,'2019-09-11',20000,1000,9000);
INSERT INTO INVOICES VALUES (102,3,1019,'2019-09-13',10000,2000,9000);

Displaying the Details of Vendor and Invoices Table:

SELECT * FROM VENDOR;

SELECT * FROM INVOICES;

Query Solution:

SELECT A.VENDORID,A.VENDORNAME,SUM(INVOICETOTAL)

FROM VENDOR A JOIN INVOICES B

ON A.VENDORID=B.VENDORID

GROUP BY VENDORID

HAVING SUM(INVOICETOTAL) = (SELECT MAX(UNPAID_DUE) FROM (SELECT SUM(INVOICETOTAL) AS UNPAID_DUE FROM INVOICES GROUP BY VENDORID) C );

Query Explanation:

Here First we are calculating sum(invoicetotal) group by each vendorid. And then we are finding the maximum of that after that we are joining the vendor and invoices based on vendorid and then by using having clause we are checking which vendorid's invoice total sum is equal to max(invoicetotal) that was calculated earlier.

Code and Output Screenshots:

Note : I have assumed that invoice total is unpaid balance so we are taking the maximum unpaid bill.

Note : If you have any queries please post a comment thanks a lot..always available to help you....