Need help with a SQL query.

Question

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.

Answer 1

I think you need to do this in two separate queries. The first gets the tax amount:

select @tax = Tax.Amount
  from Tax
    inner join ...whatever else you need here...
  where ...

Note that you can't use the 'top' clause when setting the @tax variable value - you will need to do something in your where clause to select the value you want.

Then get the sales price:

select @sales = sum(QuoteItem.SalesPrice
  from ...
  where ...

Finally return the result:

return @tax * @sales

Answer 2

According to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137110:

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