SQL SERVER – Puzzle – Incorrect Results with Decimal

June 28, 2017

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

SQL SERVER – Why We have Audit Trace in DATA Folder? What are These Files?

SQL SERVER – Puzzle – Write a Shortest Code to Produce Zero

245 Comments. Leave new

kris
July 1, 2017 8:36 pm
SELECT ID, CAST(Convert(decimal(10,2), Price*Discount)/100 AS DECIMAL (10,2)) TotalDiscount,
Price-CAST(Convert(decimal(10,2), Price*Discount)/100 AS DECIMAL (10,2)) FinalPrice
FROM #TestTable
Reply
T_xy
July 2, 2017 3:38 pm
If INT is correct model for Price and Discount, then the result is probably correct too (except for the “Price*” typo for FinalPrice). If it is not, the problem is in the schema, not the query. Btw DECIMAL(10,2) is not much better than INT, I frequently see prices lower than 0.001.
Reply
Brian Kwartler
July 2, 2017 7:20 pm
One solution is 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
Reply
groovebox
July 3, 2017 1:28 am
should be ok when you put 100.0 insread of 100
Reply
Manish Khare
July 3, 2017 10:16 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
kishan kalathiya
July 3, 2017 6:08 pm
I make this..
brawoooooooooooo……..! it 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
Reply
Madhu Nair
July 3, 2017 6:13 pm
SELECT ID
,CAST((Price*Discount)/CAST(100 AS decimal(10,2)) AS decimal(10,2))
,CAST(Price-((Price*Discount)/CAST(100 AS DECIMAL)) AS decimal(10,2))
–CAST(Price*(Price*Discount)/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
Reply
Irina
July 3, 2017 6:41 pm
This is silly to store price and discount as INT. Multiple solutions possible but I assume that those tests should teach how to do correctly rather than apply patch to bad design.
Reply
Rakhshanda Khan
July 3, 2017 6:45 pm
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
GO
Reply
Colin
July 3, 2017 7:21 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
Ray
July 3, 2017 7:39 pm
— SELECT statement (correct result)
SELECT ID, CAST(CONVERT(Decimal(10,2),Price*Discount)/100 AS DECIMAL(10,2)) TotalDiscount,
Price – CAST(Convert(decimal(10,2),(Price*Discount))/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
Reply
Chris Givan
July 3, 2017 7:43 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
–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
GO
Reply
Vinay
July 3, 2017 7:46 pm
Either you can change datatype of Column to decimal. Else solution is as below
SELECT ID, CAST(((Price*CAST(Discount AS DECIMAL))/100) AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((Price*CAST(Discount AS DECIMAL))/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
Reply
Vikash Kumar
July 3, 2017 8:20 pm
SELECT ID, CAST((Price*Discount)*1/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)*19/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
Reply
MR O AIMIUWU
July 3, 2017 9:10 pm
I answered this a week ago but surprisingly my answer was not even published.
Here again the script:
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
sanket kubade
July 3, 2017 9:35 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
Abera
July 3, 2017 9:52 pm
Created a stored procedure using while loop for ID as follows and get everything correct except missing a precision for two of my discount and price rows. Could someone comment how to fix?
create proc USP_Price_Puzzle
as
Begin
Declare @Table table (ID int, Discount float,Price float)
Declare @Number int
set @Number = 1
while @Number < 5
Begin
Insert into @Table
Select @Number,cast((@Number*5/100.0) as Decimal(10,2)),cast((@Number-@Number*5/100.0) as Decimal(10,2))
set @Number = @Number + 1
end
Select * from @Table
end
execute USP_Price_Puzzle
Reply
- Chris Givan
  July 10, 2017 9:35 pm
  Your @table columns are FLOAT datatype. Try this instead…
  Declare @Table table (ID int, Discount Decimal(10,2),Price Decimal(10,2))
  Reply
  - Abera
    July 18, 2017 7:38 pm
    That works, Thank Chris!
pavansharma
July 4, 2017 9:56 am
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
bhupesh mhashakhetri
July 4, 2017 10:52 am
Mysql : SELECT T.ID, CAST(T.Price*(T.Discount/100) AS DECIMAL(10,2)) TotalDiscount, CAST(T.Price – (T.Price*(T.Discount/100)) AS DECIMAL(10,2)) FinalPrice FROM TestTable T;
Reply
- bhupesh mhashakhetri
  July 4, 2017 11:00 am
  Sql : 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
  Reply
ѕι๋∂∂нєѕн
July 4, 2017 10:59 am
— SELECT statement (Correct result)
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