SQL SERVER – Puzzle – Incorrect Results with Decimal

It has been a while we have seen a puzzle on this blog and I keep on receiving emails and comments that you all would love to see one more puzzle. Let us see a new puzzle “Incorrect Results with Decimal”.

So the question is very simple and I expect everyone of you to get the correct answer without running the code actually in SSMS. However, due to any reason, you can’t figure out the answer, please do not hesitate to see the hints and run the entire query in SSMS.

First thing first lets us set up a table. The table contains three columns – ID, Price and Discount. We need to find out the total discount per item and final price of the product after discount.

-- Create table
CREATE TABLE #TestTable (ID INT, Price INT, Discount INT)
-- Populate table
INSERT INTO #TestTable (ID, Price, Discount)
SELECT 1, 1, 5
UNION ALL
SELECT 2, 2, 5
UNION ALL
SELECT 3, 3, 5
UNION ALL
SELECT 4, 4, 5
GO
-- Data in table
SELECT *
FROM #TestTable
GO

When we run above script we will get following answer.

SQL SERVER - Puzzle - Incorrect Results with Decimal puzzledecimal1

Now our puzzle is to find out discount value and total final price of the product.

Here is the script which is written to find TotalDiscount and FinalPrice. However, it is giving us all the answers as 0 (zero). The script is INCORRECT.

-- SELECT statement (Incorrect result)
SELECT ID, CAST((Price*Discount)/100 AS DECIMAL(10,2)) TotalDiscount,
		Price*CAST((Price*Discount)/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO

Here is the incorrect result.

SQL SERVER - Puzzle - Incorrect Results with Decimal puzzledecimal2

Puzzle Statement

Please fix the above script in such a way that it gives correct TotalDiscount and Final Price Value.

We are expecting the answer as in the following image.

SQL SERVER - Puzzle - Incorrect Results with Decimal puzzledecimal3

Puzzle Hint

If you can’t figure out the correct answer, here is another image which can potentially guide you with the correct answer.

SQL SERVER - Puzzle - Incorrect Results with Decimal puzzledecimal4

Let us see if you can give correct answer to this puzzle or not. Please share with your friends and test their knowledge about SQL as well.

NOTE: Please leave a correct answer in the comment section. I will publish all the comments together on coming Monday, till then all the answers will be hidden.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Datatype, SQL Function, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Why We have Audit Trace in DATA Folder? What are These Files?
Next Post
SQL SERVER – Puzzle – Write a Shortest Code to Produce Zero

Related Posts

245 Comments. Leave new

  • Regina Jensen
    June 28, 2017 6:57 pm

    SELECT ID, CAST((Price*Discount) * .01 AS DECIMAL(10,2)) TotalDiscount,
    Price – CAST((Price*Discount) *.01 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable

    Reply
  • Right Query – SELECT ID, CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable
    GO

    Reply
  • Grover Vivek
    June 28, 2017 7:02 pm

    — SELECT statement (Incorrect result)
    SELECT ID, CAST(CAST((Price*Discount) AS FLOAT)/100 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST(CAST((Price*Discount) AS FLOAT)/100 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable

    Hi Pinal,

    I did one correction in result query posted. Replace ‘*’ with ‘-‘ operator to get actual result for final result.

    Reply
  • select id, cast((cast(Price as decimal) * cast(Discount as decimal))/100 AS decimal(10,2)) TotalDiscount,
    price – cast((cast(Price as decimal) * cast(Discount as decimal))/100 AS decimal(10,2)) FinalPrice
    from #TestTable

    Reply
  • Grover Vivek
    June 28, 2017 7:07 pm

    Hey Pinal,

    Sorry for my first comment, actually you used ‘-‘ not ‘*’. I don’t know how I misread the operator. Any how the answer is same.

    Reply
  • George Jacob
    June 28, 2017 7:11 pm

    The statement corrected for expected result would be as follows.
    SELECT ID, CAST((Price*Discount)*1.0/100 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*Discount)*1.0/100 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable
    GO

    Reply
  • SELECT ID, Price,Discount,CAST((Price*Discount)*1.0/100 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*Discount)*1.0/100 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable
    GO

    Reply
  • Richard Polunsky
    June 28, 2017 7:14 pm

    division of two integers yields an integer, not a float. you need to promote or cast those integer arguments to float before doing the division operation.

    Reply
  • SELECT ID, CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable
    GO

    Reply
  • Sandeep Segu
    June 28, 2017 7:19 pm

    SELECT ID, CAST((Price*Discount)*1.0/100 AS DECIMAL(10,2)) TotalDiscount,
    Price – CAST((Price*Discount)*1.0/100 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable
    GO

    Reply
  • SELECT ID, CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable

    Reply
  • We can solve the issues in different way :
    Solution 1:
    Select ID, ((cast(Price as float) * cast(Discount as float))/100) as TotalDiscount, Price – ((cast(Price as float) * cast(Discount as float))/100) as FinalPrice from #TestTable

    Solution 2:
    Select ID, Cast((Price * Discount) as Decimal(10,2)) / 100 as TotalDiscount, Price – (Cast((Price * Discount) as Decimal(10,2)) / 100) as FinalPrice from #TestTable

    Solution 3:
    Select ID, Cast((Price * Discount) / 100.00 as Decimal(10,2)) as TotalDiscount, Price – Cast((Price * Discount) / 100.00 as Decimal(10,2)) as FinalPrice from #TestTable

    Reply
  • 0.99

    Reply
  • SELECT ID, (Price*Discount)/100.00 TotalDiscount,
    Price* (Price*Discount)/100.00 FinalPrice
    FROM #TestTable

    Reply
  • — SELECT statement (Incorrect result)
    SELECT ID, CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) FinalPrice
    ,(Price*Discount)/100
    FROM #TestTable
    GO

    Reply
  • robby sunder
    June 28, 2017 7:32 pm

    My answer is

    SELECT ID, CAST(CAST((Price*Discount) AS DECIMAL(10,2)) / CAST(100 AS DECIMAL(10,2)) AS DECIMAL(10,2)) TotalDiscount,
    Price – CAST(CAST((Price*Discount) AS DECIMAL(10,2)) / CAST(100 AS DECIMAL(10,2)) AS DECIMAL(10,2)) FinalPrice
    From #TestTable

    Reply
  • SELECT ID,price, discount, (Price*Discount)/100.0, CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) TotalDiscount,
    Price – CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable

    Reply
  • How about the below then you dont even need the additional * 1.0 in the missing element:
    SELECT ID, CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable

    Reply
  • Joe Rigelsford
    June 28, 2017 7:45 pm

    SELECT ID,CAST(CAST((Price*Discount) AS DECIMAL(10,2))/100 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST(CAST((Price*Discount) AS DECIMAL(10,2))/100 AS DECIMAL(10,2)) FINALPRICE
    FROM #TestTable
    GO

    Reply
  • A) The Price*CAST in the FinalPrice Field should be Price-CAST(…
    B) You need to the division by 100 to take place outside of the CAST statement (which will leave you will more decimal places than you want according to your CAST statement) or you need to multiple the product of Price & Discount by 1.0 to have the division act with decimals instead of integers or you could simplify and just change the /100 to *.01

    SELECT ID, CAST((Price*Discount)*1.0/100 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*Discount)*1.0/100 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable
    GO

    OR

    SELECT ID, CAST((Price*Discount)*.01 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*Discount)*.01 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable
    GO

    Reply

Leave a Reply