none
Sum need three decimal places

    Question

  • I have the following select statement,

    SUM(Sale * @SalesPer) AS AGComm from sales

    The problem is if @SalesPer is say .075 for 7.5 percent, it rounds to .08, which gives a wrong answer. How do I tell sql that the @SalesPer variable needs to maintain 3 decimal place?

    Thursday, September 05, 2013 7:20 PM

Answers

  • Where is the problem, in @SalesPer or in the result set from the query you written in post?

    Use decimal (10, 3) everywhere and you should be fine.


    Regards, Dean Savović

    • Marked as answer by David32 Friday, September 06, 2013 3:24 PM
    Thursday, September 05, 2013 7:27 PM

All replies

  • Where is the problem, in @SalesPer or in the result set from the query you written in post?

    Use decimal (10, 3) everywhere and you should be fine.


    Regards, Dean Savović

    • Marked as answer by David32 Friday, September 06, 2013 3:24 PM
    Thursday, September 05, 2013 7:27 PM
  • Hello David,

    Which data types do the variable @SalesPer and the column "Sale" do have? You may cast them to a more precise data type for your requirement.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 06, 2013 6:48 AM
  • Use the decimal format worked.

    SELECT        SUM(Sale * CONVERT(Decimal(10, 3), @SalesPer)) AS AGComm
    FROM            sales

    I was getting the format wrong.

    Friday, September 06, 2013 3:24 PM