1) Write a select statement that returns three columns: VendorName, InvoiceCount

Question

1)

Write a select statement that returns three columns: VendorName, InvoiceCount, and InvoiceSum.

InvoiceCount is the count of the number of invoices, and InvoiceSum is the sum of the InvoiceTotal column.

Group the result set by vendor.

Sort the result set so that the vendor with the highest number of invoices appears first.

2)

Write a SELECT statement that returns three columns: AccountDescription, LineItemsCount, and LineItemSum. LineItemCount is the number of entries in the InvoiceLineItems table that have that AccountNo.

LineItemSum is the sum of the InvoiceLineItemAmount column for that AccountNo.

Filter the result set to include only those rows with LineItemCount greater than 1.

Group the result set by account description, and sort it by descending LineItemCount.

Explanation / Answer

1) Answer:
select VendorName, count(VendorName) as 'InvoiceCount', sum(InvoiceTotal) as

'InvoiceSum' from tablename
group by VendorName
Order by 2

You can user VendorName or InvoiceTotal or ID column or any column that are

available in that table to get count of invoices

2) Question is not clear, if you have only 1 table i.e. InvoiceLIneITems then use the below code:
Answer:
select AccountDescription, count(InvoiceLineItems) as LineItemsCount, sum(InvoiceLineItemAmount) as LineItemSum from InvoiceLineItems
group by AccountDescription
Having count(InvoiceLineItems)>1

If there is a different table for Account no/account description then use this code:
select adt.AccountDescription, count(InvoiceLineItems) as LineItemsCount, sum(InvoiceLineItemAmount) as LineItemSum from InvoiceLineItems as ilt
inner join AccountDetailsTable as adt on adt.AccountNo=ilt.AccountNo
group by AccountDescription
Having count(InvoiceLineItems)>1

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.