0

I have to calculate the sum of VARCHAR2(50 byte) column in my Oracle database.

The SQL query is:

SELECT city, sum(forests) as sum_forests FROM BurnedAreas GROUP BY city

The column forests has datatype VARCHAR2(50 byte)

I've tried already some functions but they didn't work:

  1. TO_NUMBER()

  2. CAST()

I believe the problem is that the values of the specific column have different format. Some values are like 5.37, others like 14,23 etc.

Does anyone have any solution?

user3382203
  • 169
  • 1
  • 6
  • 25
ktsigkounis
  • 103
  • 1
  • 11
  • Does the comma represent a decimal point? – Tim Biegeleisen Jun 30 '17 at 06:34
  • Yes, in both situations the dot and comma represents the decimal point. – ktsigkounis Jun 30 '17 at 06:41
  • So how many different formats are there in this column? You say that the decimal separator is sometimes a comma, sometimes a point. Can there be thousand separators, too? Can there be other values, such as 'two', 'none', 'n.a.', ' ' or the like? – Thorsten Kettner Jun 30 '17 at 06:45
  • And hopefully you learn from this and change your column's data type to decimal :-) – Thorsten Kettner Jun 30 '17 at 06:47
  • @ThorstenKettner Or at the very least, use a consistent locale for the numbers. – Tim Biegeleisen Jun 30 '17 at 06:52
  • @Tim Biegeleisen: And write a constraint to reject invalid number strings? Possible, but quite a pain, I'd say :-) – Thorsten Kettner Jun 30 '17 at 06:59
  • UPDATE: I checked the values and I found thousands separator too. There is also a value with 2 commas like 1,404,27 and other with comma and dot like 1,590.42. – ktsigkounis Jun 30 '17 at 07:20
  • Given that your data is in such a poor state, I would recommend that you fix the source of your data. Handling this in Oracle is probably going to require a very ugly and complex regex replacement. – Tim Biegeleisen Jun 30 '17 at 07:21
  • I understand! Thanks for the quick responses – ktsigkounis Jun 30 '17 at 07:29
  • I fixed the values inside DB with one format. Now all values have comma as decimal separator and dot as thousands separator. For example: 5,00 - 130,00 - 4.000,00 - 150.200.000,00. In function to_number() what's the specific format mask ? – ktsigkounis Oct 09 '17 at 08:55

3 Answers3

1

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='',.'''))

Eric Aya
  • 69,473
  • 35
  • 181
  • 253
ktsigkounis
  • 103
  • 1
  • 11
0
SELECT city,sum(to_number(replace(translate(forests, '(', '-'), ')', ''))) AS sum_forests
FROM BurnedAreas
GROUP BY city

Generally one need to check how the data flow is there for "forests" column.
Accordingly need to include the condition inside translate(...)

Hope that helps!!!
priya raj
  • 362
  • 2
  • 8
0

Below code will skip non numeric data and you will not get error.

SELECT city, sum(forests) as sum_forests 
FROM BurnedAreas 
where ISNUMERIC (forests)=1
GROUP BY city

OR

Insert all data in temp table & then do sum. becuase your column having "," which is making value invalid.

SELECT city, replace(forests,',','') as forests 
 into #BurnedAreas
 FROM BurnedAreas

Then run below data.

SELECT city, sum(forests) as sum_forests 
FROM #BurnedAreas 
GROUP BY city
Tripurari Yadav
  • 216
  • 1
  • 3
  • 11
  • But this is a bit like throwing the baby out with the bathwater, because then the sum would not reflect the true logical value. – Tim Biegeleisen Jun 30 '17 at 08:10