Need help with a SQL query.

Question

question

Shimmy asked Dec 13, '09

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-2005 t-sql query

10 |1200

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

Answer 1 · 2009-12-14T05:05:28Z

Squirrel 1 answered Dec 14, '09

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

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

Answer 2 · 2009-12-13T11:37:43Z

Dinesh Kumar answered Dec 13, '09

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

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

question