Problem with query, VIEW clause and TOP clause

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

Latest Posts