View clause and Top clause queries

Posted by Retrans14 under Sql Server on 7/15/2012 | Points: 10 | Views : 2944 | Status : [Member] | Replies : 1
I'm getting error with a query and would really appreciate some help.
I have to Create a view named TOP10PaidInvoices that returns three columns for each vendor: VendorName, LastInvoice (the most recent invoice date), and SumOfInvoices (the sum of the InvoiceTotal column). Return only the 10 vendors with the largest SumOfInvoices and include only paid invoices.
Here is my statement but I get some errors. Would like to know what is wrong with it.

CREATE VIEW TOP10PaidInvoices
AS
SELECT TOP 10 Vendors.VendorName, Invoices.LastInvoice AS InvoiceDate,
Invoices.SumOfInvoice AS InvoiceTotal,
FROM Invoices INNER JOIN Vendors ON Invoices.InvoiceID = Vendors.VendorID
ORDER BY SumOfInvoices ASC

Here are the errors I am getting
Msg 8120, Level 16, State 1, Procedure TOP10PaidInvoices, Line 2
Column 'Vendors.VendorName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 207, Level 16, State 1, Procedure TOP10PaidInvoices, Line 2
Invalid column name 'LastInvoices'.
Msg 207, Level 16, State 1, Procedure TOP10PaidInvoices, Line 3
Invalid column name 'SumOfInvoices'




Responses

Posted by: Pandians on: 7/16/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check it out!
CREATE VIEW TOP10PaidInvoices

AS
SELECT TOP 10 Vendors.VendorName, MAX(Invoices.InvoiceDate) AS LastInvoice,
SUM(Invoices.InvoiceTotal) SumOfInvoice,
FROM Invoices INNER JOIN Vendors ON Invoices.InvoiceID = Vendors.VendorID
GROUP BY Vendors.VendorName
ORDER BY 3 DESC
Not sure about the column name, change it accordingly!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Retrans14, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response