Sum of Data in Varchar Column

How to sum varchar datatype by converting varchar into float

Try like this:

SELECT sum(CAST(amount AS UNSIGNED)) FROM tbl1

Note that UNSIGNED and SIGNED are used for Integer. If your data may contain decimal values then you can instead try to use decimal like this:

SELECT sum(CAST(amount AS DECIMAL(10,2))) FROM tbl1

Sum of data in varchar column

try this

select sum(cast(replace(SCENARIO1, ',', '.') as decimal(29, 10)))
from Mnt_Scenario_Exercice
where code_pere = '00000000';

If you couldn't convert your '4582014,00' into decimal, there's a chance you have different decimal separator on your server. You could look what it is or just try '.'

How to sum time spans stored in a varchar column?

A VARCHAR may not be the most efficient version of storing time, but with a bit of casting you can do what you're looking for;

WITH cte AS (
  SELECT name, SUM(DATEDIFF(MINUTE, '00:00', CAST(WorkingHr AS TIME))) t
  FROM mytable GROUP BY name
)
SELECT name, RTRIM(t/60) + ':' + RIGHT('0' + RTRIM(t%60), 2) FROM cte

An SQLfiddle to test with.

What it basically does is to cast the string to a TIME, then using DATEDIFF to convert the time to minutes. It then just basically sums up the minutes per name.

The outer query just converts minutes to a HH:MM string using simple string operations.

If you're not looking to group by name, the query can be simplified to;

WITH cte AS (
  SELECT SUM(DATEDIFF(MINUTE, '00:00', CAST(WorkingHr AS TIME))) t FROM mytable
)
SELECT RTRIM(t/60) + ':' + RIGHT('0' + RTRIM(t%60), 2) FROM cte;

...which is pretty much the same query without grouping.

How to sum varchar column in SQL?

This is a little bit of an XY solution, given that it produces rows and not columns. But this is more "database-y", and essentially what you have is a PIVOTed version of the following anyway.

You can't add words, but you can count them. So just GROUP BY your Gender column and COUNT:

select Gender, count(*)
from EmployeeDetails
group by Gender

converting varchar to number and sum gives error:

demo

using replace(string,FromValue,ToValue)

NOTE: this assumes you have amount in a ###,###.00 format. if not you could return bad data.

with tb AS 
(SELECT '1,000.01' as "amount", '01-01-2022' as "date",'z' as "from merch" UNION ALL
SELECT '10,000.02' as "amount", '01-01-2022' as "date",'z' as "from merch" UNION ALL
SELECT '100,000.02' as "amount", '02-01-2022' as "date",'y' as "from merch")

SELECT date_trunc('day', to_date("date" , 'mm-dd-yyyy')) as day, 
"from merch", 
sum(CAST(replace("amount",',','') AS numeric)) as amount 
FROM tb 
GROUP BY 1,2 
ORDER BY 1,2;

Demo 2 with decimal

sum(CAST(replace("amount",',','') AS decimal(10,2))) as amount 

This is assuming you've identified the problem correctly.

I (not so recently) found an exchange in comments humorous:

• If you EVER need to do math on it, store it as a number
• If you'll NEVER do math on it store it as string
• If it needs to do both, you need two columns
• Except for dates... store dates as dates period and use date functions on them... not string {shudder} functions!
• and AutoIncrements can be numbers (though we should never do math on them)

@xQbert -- this shall henceforth be referred to as "xQbert's razor"

sql sum + varchar

No need to add Group by Clause.

No other columns in Select other than column used in Aggregate function then no need to have GROUP BY.

select sum(cast(Marks as integer))
from Results    
where Marks <>'Absent'

Oracle: Find sum of a VARCHAR2(50 byte) column

As I mentioned the data in the VARCHAR column had a format like 4.900,25 where dot was hundred separator and comma decimal separator (2 decimal digits).

So to find the sum, I used TO_NUMBER function with some more parameters.

SUM(TO_NUMBER(forests,'999G999G999D99','NLS_NUMERIC_CHARACTERS='',.'''))

SUM on VARCHAR field converted to DECIMAL not adding numbers after decimal

You are not using the DECIMAL datatype accordingly to your use case. DECIMAL(16, 16) declares a decimal number with a total of 16 digits and with 16 decimal digits. This cannot hold a value greater than 1.

Consider:

SELECT CAST('1.12345678' AS DECIMAL(16, 16)) 

Returns: 0.9999999999999999.

You probably want something like DECIMAL(16, 8) instead, since your strings seem to have 8 decimals.

---

From the MySQL documentation:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

• M is the maximum number of digits (the precision). It has a range of 1 to 65.

• D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

How to get sum of string data in SQL?

SELECT 
convert(char(20),dateadd(second,SUM 
( 
DATEPART(hh,(convert(datetime,Working_hours,1))) * 3600 +
DATEPART(mi, (convert(datetime,Working_hours,1))) * 60 + 
DATEPART(ss,(convert(datetime,Working_hours,1)))),0),108
)
AS Working_hours
FROM tblTime 

You can use this Query....In Sql server..

---

Related Topics