question

RizalValry avatar image
0 Votes"
RizalValry asked RizalValry commented

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

I Have data types of table dbo.coba as following :
132094-design-dbo.png

I want SUM and group by from query as following :
132083-dbotablecoba.png

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

and result :
132025-convert-from-replace-varchar-to-int-and-then-sum.png

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


[4]: /answers/storage/attachments/132018-convert-from-replace-varchar-to-int-and-then-sum.png

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered RizalValry commented

Hi @RizalValry,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I Get Solutions :

SELECT nama,
SUM(TRY_CONVERT(decimal(20, 0), REPLACE(jumlah, ',', ''))) as total
FROM dbo.coba
GROUP BY nama, jumlah


Thanks a lot for your help @MelissaMa-msft :)

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

Money supports comma as a thousand delimiter.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @RizalValry,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MelissaMa-msft nice suggestion

but when I Have big data in one table, I find an error in the query you gave:

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

error message :
Msg 8114, Level 16, State 5, Line 58
Error converting data type varchar to bigint.

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

error message :
error message :
Msg 8114, Level 16, State 5, Line 58
Error converting data type varchar to numeric


is there any other solution ?

0 Votes 0 ·

Sorry, I Found other trouble like :

Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.


0 Votes 0 ·