Help me Please, How to Take Replace "," and Convert from varchar to int then Sum(values) at the same time in SQL Server

Question

I Have data types of table dbo.coba as following :

I want SUM and group by from query as following :

But, when I exec query as :
select
nama,
SUM(CAST(REPLACE(jumlah, ',','') AS bigint))
from
dbo.coba
group by nama, jumlah

and result :

My hope is that each name produces a total number of them all by group by name and the number becomes one group, please help me

Answer 1

Hi @Rizal Valry ,

Could you please provide one big data you have?

I tried to insert a new row as below:

insert into coba (nama,jumlah) values  
('rizal','120,784,852,841,157,413.000')  

I received 'Error converting data type varchar to bigint.' error after executing below query.

select  
nama,  
sum(cast(replace(jumlah, ',', '') as bigint))  
from  
dbo.coba  
group by nama  

The storage of bigint is 8 Bytes and range is -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).

You could try with decimal(38,2) instead of bigint.

select  
nama,  
sum(cast(replace(jumlah, ',', '') as decimal(38,2)))  
from  
dbo.coba  
group by nama  

Above is working from my side.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try group by nama instead of group by nama, jumlah.

Answer 3

You can save yourself some trouble and use:
SUM(CAST(jumlah AS money))

Money supports comma as a thousand delimiter.

Answer 4

Hi @Rizal Valry ,

If you would like to get a total number of each name, you only need to group by one column name.

As already mentioned by other experts, you could remove the jumlah column from group by part.

You could also use SUM(...) Over (Partition by ...) instead as below:

select distinct   
nama,  
SUM(CAST(REPLACE(jumlah, ',','') AS bigint)) over (partition by nama) total  
from  
dbo.coba  

In addition, we use “Money” as a data type for a currency column. But instead of Money, DECIMAL (19, 4) is more recommended since MONEY has essentially the same definition, but it needs to be used with caution.

Please also refer below using decimal instead of bigint:

select  
nama,  
sum(cast(replace(jumlah, ',', '') as decimal(18,2)))  
from  
dbo.coba  
group by nama  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.