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
— 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
FROM #TestTable
GO
multiply price times 1.0 to cast the equations from integer to float.
— SELECT statement (Incorrect result)
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
SELECT ID ,
CAST(( Price * Discount ) AS DECIMAL(10, 2)) / 100 TotalDiscount ,
Price * CAST(( Price * Discount ) AS DECIMAL(10, 2)) / 100 FinalPrice
FROM #TestTable;
GO
Multiply with .01 to make it explicit calculate values upto 2 decimal
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
multiply it by 1.0 to make implicitly convert it to decimal
select CAST(CAST(Discount as FLOAT)/100 * CAST(Price as FLOAT) as DECIMAL(10,2)) as TotalDiscount,
CAST(CAST(Price as FLOAT) – CAST(Discount as FLOAT)/100 * CAST(Price as FLOAT)as DECIMAL(10,2)) as FinalPrice from #TestTable
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
Hi Dave, the queries change in the post (price – cast vs price * cast). One trick I do is to convert the denominator into the desired type as seen below:
SELECT
ID
,Price
,Discount
,CAST((Price*Discount)/cast(100 as float) AS DECIMAL(10,2)) TotalDiscount
,Price-CAST((Price*Discount)/cast(100 as float) AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
However I submitted a request to MS to re-review how they handle conversions between float/varchar which you may find fun to test.
https://docs.microsoft.com/en-us/collaborate/connect-redirect
Hi Dave (thanks for the awesome site from the last two years)… below is the trick I use … to cast the denominator as float or another type then handle the math. I may have double posted.
SELECT
ID
,Price
,Discount
,CAST((Price*Discount)/cast(100 as float) AS DECIMAL(10,2)) TotalDiscount
,Price-CAST((Price*Discount)/cast(100 as float) AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
However MS has odd issues when converting between float/varchar often distorting data with scientific notation between conversions which can be painful especially if using “Excel” or another application when importing a table through the GUI or SSRS (parameter sniff > detects field as float > when it is varchar… change some data to scientific notated values such as a MEmber or customer ID…)
I opened a request here:
https://docs.microsoft.com/en-us/collaborate/connect-redirect
— Most obvious 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
GO
— Alternative Answer #1
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
— Alternative Answer #2
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
You’re doing an INT division before casting to decimal. Cast the value first.
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
Multiply by 1.00
Hey Pinal I did paste it into SSMS to test it but here’s my answer. Can’t be doing integer division, need to throw a decimal number into the mix.
— SELECT statement (hopefully 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
GO
(Price*Discount) * 1.0
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
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
Missing Element is Need to Convert (Price * Discount) Result to Decimal then Divide by 100 and Again Cast to Decimal for two Decimal value