question

Shimmy avatar image
Shimmy asked

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.                    
sql-server-2005t-sqlquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered

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)            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dinesh Kumar avatar image
Dinesh Kumar answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.