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
Hi Pinal,
This is incorrect script because all the values (Price, Discount and 100) are integer. So the result will also be integer. We are casting the result so if result is 0 then casting will also give 0.00 :).
The solution is to convert any one value to decimal before the calculation.
So we can use any of the following queres:
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
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
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
— Another Experiment:
— You just copy paste below in new SQL query window and checked it.
IF OBJECT_ID(‘tempdb..#TestTable’) IS NOT NULL
BEGIN
DROP table #TestTable
END
— 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
— SELECT statement (Incorrect result)
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
— OUTPUT
–ID TotalDiscount FinalPrice
–1 0.05 0.05
–2 0.10 0.20
–3 0.15 0.45
–4 0.20 0.80
GO
— OR
SELECT ID, (Price*Discount) / CAST(100 AS DECIMAL(10,2)) TotalDiscount,
Price* (Price*Discount) /CAST(100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
— Result
–ID TotalDiscount FinalPrice
–1 0.05000000000 0.05000000000
–2 0.10000000000 0.20000000000
–3 0.15000000000 0.45000000000
–4 0.20000000000 0.80000000000
Because both Price & Discount columns in the table are INT type, hence the result of dividing (Price * Discount) by 100 results in int type due to which it shows 0. We need to first convert numerator into decimal type & then divide it.
So, we need to rewrite query as:
SELECT ID, CAST(cast(Price*Discount as decimal(18,2))/100 AS DECIMAL(10,2)) TotalDiscount,
Price – CAST(cast(Price *Discount as decimal(18,2))/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
as decimal(10,2)
My answer is:
Select id, CONVERT(Decimal(10,2), (Price *Discount)/100.00) as TotalDiscount,
Price-CONVERT(Decimal(10,2), (Price *Discount)/100.00) as FinalPrice
From #ItemPrice
— Correct Results
— 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
SELECT ID, CAST((CAST(Price AS DECIMAL(9,2))*CAST(Discount AS DECIMAL(9,2)))/100 AS DECIMAL(9,2)) TotalDiscount,
        Price – CAST((CAST(Price AS DECIMAL(9,2))*CAST(Discount AS DECIMAL(9,2)))/100 AS DECIMAL(9,2)) FinalPrice
FROM #TestTable
GO
DROP TABLE #TestTable
Hi Pinal,
Correct script is below.
SELECT ID, CAST(((Price*Discount))As decimal(10,2))/100 TotalDiscount,
Price – CAST(((Price*Discount))As decimal(10,2))/100 FinalPrice
FROM #TestTable
Thanks,
Dattatray Pise.
Corrected query :-
SELECT ID
, (Price*Discount)/Cast(100 AS DECIMAL(5,2)) TotalDiscount
,Price*(Price*Discount)/Cast(100 AS Decimal(5,2)) FinalPrice
FROM #TestTable
GO
We’ve to use Cast after the divide symbol to get the correct values. I’ve tried too attach the screenshot but unable to paste here
Think, it will work:-
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)/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)*0.99/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)*0.99/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
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
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
SELECT ID, CAST((Price*Discount) AS DECIMAL(10,2))/100 TotalDiscount,
Price-CAST((Price*Discount) AS DECIMAL(10,2))/100 FinalPrice
FROM #TestTable
ID TotalDiscount FinalPrice
1 0.050000 0.950000
2 0.100000 1.900000
3 0.150000 2.850000
4 0.200000 3.800000
Expected Query:
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
Change the 100 to 100.0 to force the math to use decimal
— 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
GO
(The FinalPrice calculation in the original post also contained an error — should be subtract the TotalDiscount)
SELECT ID, CAST((Price*Discount)*1.0/100 AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((Price*Discount+0.0)/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
You can fix it with “)*1.0/100” or “+0.0)/100”
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
here your given query and hint query is different,second column there is price * in your first query and price – in yoyr hint query hint query is right.
SELECT id,cast((discount*price) as decimal(10,2))/100 as discount,
(price-(cast((discount*price) as decimal(10,2))/100)) as afterPrice
FROM #TestTable