Question

Write a query to answer the question: Which vendors are being paid from more than one account? To answer this question, retrieve VendorName and total number of accounts that apply to that vendor’s invoices. Name the total number of accounts as "TotalNumAccounts". (Hint: count AccountNo in InvoiceLineItems table).

Answer 1

Assuming that the relevant tables in the database are "Vendor" and "InvoiceLineItems" and that the "Vendor" table has a primary key "VendorID" that is also present as a foreign key "VendorID" in the "InvoiceLineItems" table, the query to answer the question would be:

SELECT Vendor.VendorName, COUNT(DISTINCT InvoiceLineItems.AccountNo) AS TotalNumAccounts

FROM Vendor

INNER JOIN InvoiceLineItems ON Vendor.VendorID = InvoiceLineItems.VendorID

GROUP BY Vendor.VendorName

HAVING COUNT(DISTINCT InvoiceLineItems.AccountNo) > 1;

Step-by-step explanation:

The query uses an inner join to combine the Vendor and InvoiceLineItems tables based on the standard VendorID column. It then groups the results by VendorName and uses the COUNT and DISTINCT functions to count the individual accounts associated with each vendor's invoices. Finally, the HAVING clause filters the results only to include vendors with more than one account associated with their invoices.

The result set will include two columns: VendorName and TotalNumAccounts. The VendorName column will list the names of vendors being paid from more than one account, and the TotalNumAccounts column will show the number of accounts that apply to that vendor's invoices.