SQL Puzzle | The Varchar column and comma Puzzle

In this puzzle we have a table with 2 columns – id and Vals. You have to sum the values of column val group by Id column. The problem here is the data type of the Vals column is varchar. So you cannot do sum on a varchar column. The problem came due to the incorrect choice of the data type. This has been asked in a recent technical discussion.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE SumofValues
(
	 Id INT
	,Vals VARCHAR(100)
)
GO

INSERT INTO SumofValues VALUES (1,'900.0'),(1,'1,076.6'),(2,'2,076.6'),(3,'112.44'),(3,'1,076.6')
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution 1

--

SELECT ID,SUM(CAST(Vals AS MONEY)) SumofVals FROM SumofValues	 	
GROUP BY ID

--

Solution 2

--

SELECT ID,SUM(CAST(REPLACE(Vals,',','') AS DECIMAL(9,2))) SumofVals FROM SumofValues GROUP BY ID

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com