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)

, , ,
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

  • Amit Srivastava
    July 4, 2017 11:05 am

    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
  • Bidul Areekara
    July 4, 2017 12:44 pm

    SELECT ID,
    CAST(Price*Discount AS decimal(10,2)) /100 AS TotalDiscount
    ,CAST( (ID+PRICE)*DISCOUNT AS DECIMAL(10,2)) /10 -(CAST(Price*Discount AS decimal(10,2)) /100) FinalPrice
    FROM #TestTable

    Reply
  • Julian Valoo
    July 4, 2017 1:08 pm

    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
    GO

    Reply
  • Julian Valoo
    July 4, 2017 4:37 pm

    SELECT ID, cast(CAST((Price*Discount) as float)/100 AS DECIMAL(10,2)) TotalDiscount,
    CAST((Price+price)*Discount AS float)/10 – cast(CAST((Price*Discount) as float)/100 AS DECIMAL(10,2)) as FinalPrice
    FROM #TestTable
    GO

    Reply
  • Julian Valoo
    July 4, 2017 5:47 pm

    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
    GO

    Reply
  • Cast Price and Discount to Money

    Reply
  • 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
    FROM #TestTable

    Reply
  • Kailash Ameta
    July 5, 2017 9:13 am

    Actually the issue is that every integer mathematics yields only integer results. When results is divided by 100 and none of the calculation is more than 100 so when dividing by 100. It is yielding 0 in order to get correct result you need to convert integer into decimals. This can be achieved in one of the two ways either by converting participating fields in decimal or by multiplying one of the participating fields by 1.0, so correct result can be obtain.

    As when mathematical calculation is performed then it follows waterfall model.

    Reply
  • Benno Janssen
    July 5, 2017 12:58 pm

    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
  • 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
    FROM #TestTable

    Reply
  • Phil Roberts
    July 5, 2017 9:54 pm

    Simply move the parens to force conversion of Discount to Decimal BEFORE any other operations, that’s all. No additional elements needed.

    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
  • The issue has several ways you can fix it including changing the tables data type to match expected outcome. The root issue is implicit versus explicit casting. In the case of an integer divided by an integer, the implicit outcome is integer, thus no decimal positions in the results. If you desire a specific outcome you are better off doing an explicit conversion to ensure correct typing throughout. As to the best way, that depends on what you want to choose to do but dividing by 100.00 should be simplest although if you are talking money anyway it might make more sense to make the stored values inline with their meaning.

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

    Reply
  • Lincoln Borges de Castro
    July 24, 2017 7:44 pm

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

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

    –this really works… not the above, sorry

    Reply
  • after see this remind my collage day where i learn c in c
    When you manipulate a data type then output is always that data type.

    In this script
    SELECT ID, CAST((Price*Discount)/100 AS DECIMAL(10,2)) TotalDiscount,
    Price*CAST((Price*Discount)/100 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable

    Price and Discount is data type is int. Also 100 data type is int
    Ex: let price is 1 and discount is 5
    then
    (1*5)/100=0
    then cast 0 to decimal give 0.00
    So right answer is
    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
    OR
    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
  • Just change the 100 to 100.00, makes it decimal not integer

    Reply
    • yes it works

      Reply
      • Exactly the /100 is a hardcoded constant so just change it to /100.00 and you correct the distance results. For the FinalPrice it should be subtraction not multiplication. Price-CAST((Price*Discount)/100.00 AS DECIMAL(10,2))

  • SELECT ID,price,discount, CAST((Price*cast(Discount as float)) /100 AS DECIMAL(10,2)) TotalDiscount,
    Price-CAST((Price*cast(Discount as float))/100 AS DECIMAL(10,2)) FinalPrice
    FROM #TestTable
    GO

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

    Reply
  • Rolando Almaguer
    October 21, 2019 9:00 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

Leave a Reply

Menu