Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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)
    Wendell

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •