Question

SQL SERVER

Write a SELECT statement that return the vendor name and the total number of accounts that apply to that vendor’s invoices. Filter the result set to include only the vendor who is being paid more than twice. (HINT: use Vendors table, Invoices table and InvoiceLineItems table).

TABLES:
Vendor

Columns: VendorID, VendorName, DefaultTermsID, DefaultAccountNo

Invoices

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

InvoiceLineItems

Columns: InvoiceID, InvoiceSequence, AccountNo, InvoiceLineItemAmount, InvoiceLineItemDescription

Answer 1

Please use below query:

SELECT v.VendorName,

COUNT(DISTINCT l.AccountNo) AS [Total Number Of Accounts]

FROM Vendor v JOIN Invoices i

ON v.VendorID = i.VendorID

JOIN InvoiceLineItems l

ON i.InvoiceID = l.InvoiceID

GROUP BY v.VendorName

HAVING COUNT(DISTINCT l.AccountNo) > 2

ORDER BY v.VendorName;

Explanation: We have to display VendorName and total number of Accounts, filtered by the vendor who has been paid more than twice

Alias for Vendor table is v, Invoices is i and InvoicesLineItems is l.

First line is used to give you Vendor Name and Second line is used to give you total number of accounts. You have to use inner join between Vendor and Invoices table o VendorId column to find the Invoices for Vendors and then result set would be inner joined with InvoicelineItems on InvoiceId using Group by clause on Vendor name to aggregate the Vendor Name based on Account No and then apply the filter as greater than 2. It should give you the desired output.

Screen sot of query for your reference:

Data in InvoiceLineItems table is as follows:

Data in Invoices is as follows:

Data in Vendor is as follows:

Output:

In case of any query, do comment.

Please rate the answer. Thanks.

Answer 2

Write a SELECT statement that return the vendor name and the total number of accounts that apply to that vendor’s invoices.

We have the following keys in the tables given:

Vendor: VendorID (Primary Key)

Invoices: InvoiceID, VendorID (Foreign Key - references VendorID from Vendor table)

InvoiceLineItems: InvoiceID (Foreign Key - references InvoiceID from Invoices table)

We can select VendorName directly from the Vendor table but in order to find the number of accounts which are related to a Vendor, we need to use the AccountNo field in the InvoiceLineItems table, which is indirectly related to Vendor through the Invoices table. The number of accounts can be found out be counting the distinct number of AccountNo that are related to a Vendor. The relationship is as follows:

(the left side of the period denotes the tablename, and the right side denotes column name)

InvoiceLineItems.InvoiceID => Invoices.InvoiceID =>  Invoices.VendorID => Vendor.VendorID

Such a relationship leads use to use joins to get a result.

The query is:

SELECT
   VendorName,
   COUNT(DISTINCT InvoiceLineItems.AccountNo) AS Accounts
FROM Vendors
JOIN Invoices
   ON Vendors.VendorID = Invoices.VendorID
JOIN InvoiceLineItems
   ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
GROUP BY Vendor.VendorID;

Filter the result set to include only the vendor who is being paid more than twice.

The above query can be modified to filter those Vendors which are being paid more than twice (more than two accounts), We use the HAVING keyword because results of aggregate functions (COUNT in this case) can only be filtered using the HAVING clause.

The query is:

SELECT
   VendorName,
   COUNT(DISTINCT InvoiceLineItems.AccountNo) AS Accounts
FROM Vendors
JOIN Invoices
   ON Vendors.VendorID = Invoices.VendorID
JOIN InvoiceLineItems
   ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
GROUP BY Vendor.VendorID
HAVING COUNT(DISTINCT InvoiceLineItems.AccountNo) > 2;

The above query is the final query.

Feel free to ask any doubts in the comments :)