x

Need help with a SQL query.

SELECT @Tax = SUM(QuoteItem.SalesPrice) * TOP (1) Tax.Amount                     
FROM Tax INNER JOIN                    
      Job ON Tax.TaxId = Job.TaxId INNER JOIN                    
      Quote ON Job.JobId = Quote.JobId INNER JOIN                    
      QuoteItem INNER JOIN                    
      Room ON QuoteItem.RoomId = Room.RoomId ON Quote.QuoteId = Room.QuoteId                    
WHERE (Room.QuoteId = @QuoteId) AND (QuoteItem.UnitId = @UnitId)                    
    RETURN @Tax                    


Result:

Msg 156, Level 15, State 1, Procedure fn_GetQuoteUnitTax, Line 54                    
Incorrect syntax near the keyword 'TOP'.                    

Note, that when I omit the TOP(1) it says:

Msg 8120, Level 16, State 1, Procedure fn_GetQuoteUnitTax, Line 54
Column 'Tax.Amount' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.
more ▼

asked Dec 13, 2009 at 10:29 AM in Default

Shimmy gravatar image

Shimmy
3 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

you shouldn't be using TOP (1) there. Doing so you are assuming you have only 1 single tax id in your table and your calculation will be wrong when you have more than 1 tax amount

You should multiply it before sum it up.

SELECT @Tax = SUM(QuoteItem.SalesPrice * Tax.Amount)
more ▼

answered Dec 14, 2009 at 05:05 AM

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

(comments are locked)
10|1200 characters needed characters left

Hi,

I Understand your requirement is from tax table you are looking for all job and there respective top tax details. eg.

Job         Tax            
001         100            
002         050            
003         200            
004         250            

if this is what your tax table should return, correct your sql in the following way.

SELECT @Tax = SUM(QuoteItem.SalesPrice) * Tax.Amount             
FROM (Select JobId, Max Amount from Tax group by JobId)Tax INNER JOIN            
      Job ON Tax.TaxId = Job.TaxId INNER JOIN            
      Quote ON Job.JobId = Quote.JobId INNER JOIN            
      QuoteItem INNER JOIN            
      Room ON QuoteItem.RoomId = Room.RoomId ON Quote.QuoteId = Room.QuoteId            
WHERE (Room.QuoteId = @QuoteId) AND (QuoteItem.UnitId = @UnitId)            
    RETURN @Tax            

Hope this will help you

Thanks and Regards
Dinesh

more ▼

answered Dec 13, 2009 at 11:37 AM

Dinesh Kumar gravatar image

Dinesh Kumar
11 2 2 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1943
x985
x369

asked: Dec 13, 2009 at 10:29 AM

Seen: 888 times

Last Updated: Dec 13, 2009 at 10:29 AM