Problem with query, VIEW clause and TOP clause

Posted by Sweetpepper66 under Sql Server on 11/12/2011 | Points: 10 | Views : 1784 | Status : [Member] | Replies : 2
Hello,

Not sure If I can post this here, but going to, so sorry if I am not in the right forum.

I have a problem 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 and I am a little confused. Would like to know what is wrong with it if I may ask.

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

Thank you in advance




Responses

Posted by: Blessyjees on: 11/14/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi,
are you getting the result. If you have any errors , please specify.

Blessy Baby
Digitalmesh Softech pvt Ltd
https://blessybaby.wordpress.com/

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

Posted by: Sweetpepper66 on: 11/14/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Thanks for your reply, yes I am getting an error and been trying to fix it but no result. Here is the error
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'.

I was wondering if I may want to do a subquery???

Thanks for your help


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

Login to post response