Formula to Sum 2 columns is sql Server 2005

Topic

New Reply

Hi, I have the following formula which is working fine:
SELECT SUM(ItemPrice) AS [Total Price], CustomerID, SUM(SalesTax) AS [Sales Tax]
FROM tblShutterbugOrder
WHERE (CustomerID = @CustomerID) AND (Paid = 0)

I need to add a 4th column that will return the sum of [ItemPrice] + Sum of [salestax]. Everything I try gives my syntax errors.

Thanks,
Leesha

Reply | Quote

Replies

I believe the syntax should be something like this:

Code:

SELECT SUM(ItemPrice) AS [Total Price], CustomerID, SUM(SalesTax) AS [Sales Tax], SUM(ItemPrice) + SUM(SalesTax) AS [Amount Due]
FROM tblShutterbugOrder
WHERE (CustomerID = @CustomerID) AND (Paid = 0)

Reply | Quote

I agree with Wendell.

Strangely with SQL although you can reference a derived field in the Order By Clause,
You cannot reference it as part of another calculation.

So ORDER BY [Total Amount] Is Acceptable

But [Total Price] + [Sales Tax] As [Total Amount] is not

I do assume that you just omitted the Group By from the detail submitted

Reply | Quote

LOL! When it comes to me with sql Server don’t assume anything. It’s only by luck when I get things to work. I used Wendell’s sql and it works as long as CustomerID and Paid are not in the query. Once I put either or both of those I get an error related to “group by” which I never would’ve gotten past had it not been for your hint above. Once I changed that I was set.

Thanks to both of you!
Leesha

Reply | Quote