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.
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.
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.
Puzzle Hint
If you can’t figure out the correct answer, here is another image which can potentially guide you with the correct answer.
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)
245 Comments. Leave new
instead of 100 write 100.00 or 100.0 or 100.
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
–Correct
SELECT ID, CAST( (Price * (Discount / 100.00)) AS DECIMAL(10,2)) TotalDiscount,
CAST(( Price – Price * (Discount/100.00)) AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
/100.0
SELECT ID, CAST((Price * Discount)*.01 AS DECIMAL(10,2)) TotalDisCount,
Price-CAST((Price * Discount)*.01 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
1 0.05 0.95
2 0.10 1.90
3 0.15 2.85
4 0.20 3.80
SELECT ID, CAST((PRICE*DISCOUNT) AS DECIMAL(10,2))/100 TOTALDISCOUNT,
PRICE*(CAST((PRICE*DISCOUNT) AS DECIMAL(10,2))/100 ) FINALPRICE
FROM #TESTTABLE
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
Can we do like this
SELECT ID, CAST((Price*Discount) AS DECIMAL(10,2))/100 TotalDiscount,
Price*CAST((Price*Discount)AS DECIMAL(10,2))/100 FinalPrice
FROM #TestTable
GO
This can also use so in my previous comment there are double casting.
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
Solution1:
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
Solution2:
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
Instead of casting only result, you can cast each columns to decimal then it will give correct result.
This is the correct answer
— SELECT statement (correct result)
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
Hi Dave,
I am not sure what did you provide in the “hint” screenshot. Most probably it is “*1.0″ there to make it numeric. However it is quite easier just to divide by 100.0 not by 100.
And also you have an mistake in the ” Price*CAST((Price*Discount)/100 AS DECIMAL(10,2)) FinalPrice”. It is supposed to be Price-CAST….
SELECT ID, CAST((Price*Discount)*0.1*10/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)*0.1*10/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
There isn’t a missing element here and you don’t need to add one. The multiplication works fine, no matter what. The problem comes when it’s time to divide by 100. That’s being done using integer math and produces the incorrect results. All that needs to be done is to change the value of “100” to “100.0” everywhere in the code. That will help performance a smidge because you’re NOT having to add another calculation in the form of 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
GO
Ah… sorry. You want to see code. Using your original “incorrect” code, we not only get the incorrect answer because of Integer math, but we get the incorrect answer because you multiplied for final price instead of subtracting. Fixing that error then, like I said, you only need to change the “100”s to “100.0”s. No additional element required.
Here’s the code. I did reformat the code a bit, as well. Let’s hope the indentation sticks on this forum.
–===== SELECT statement (Correct result)
SELECT ID
,TotalDiscount = CAST((Price*Discount)/100.0 AS DECIMAL(10,2))
,FinalPrice = Price – CAST((Price*Discount)/100.0 AS DECIMAL(10,2))
FROM #TestTable
;
Hi, I think this is the correct answer:
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
Because of this, the previous query was incorrect because SQL, by default, considers the expression (Price*Discount) integer and int/int = int. And conversion is done after division.
cast(price*discount as real)/100
Below query is your incorrect statement.
— 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
Below query is correct.
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
you have not use another cast function for price*discount and you have multiply the price value into converted value in Finalprice.
We have to use another cast function for price*discount value as decimal and for Final Price we have to subtract the Price value into the converted values then only we can get the output as you mentioned.