-1

I want to calculate the average two columns but each column has string values in between and it is creating an error when avg function is applied.How can i solve this issue?

priya
  • 1
  • Please share your code – PinkeshGjr Jan 22 '19 at 09:03
  • You could add a where-clause indicating the column value should be numeric – Robert Kock Jan 22 '19 at 09:20
  • Please [edit](https://stackoverflow.com/posts/54304335/edit) your question to include a [MCVE] containing: some sample data; your expected output for that sample data; an English description of how you got to that expected output (if it is not obvious); your attempt at the solution; and the error messages it generated. – MT0 Jan 22 '19 at 10:03

3 Answers3

0

This may work

Select (cast(col1 as float) + cast(col2 as float)) / 2 as average_between_col1_and_col2, 
        avg(cast(col2 as float)) as avg_col_2, 
        avg(cast(col1 as float)) as avg_col_1
from table tbl

In any case please provide a complete example with sample data, desired result query you did and error you got.

NOTE my query assumes both columns not to be numeric but containing numbers, if a column is already numeric you can omit the cast.

DDS
  • 2,340
  • 16
  • 34
0

Sample data would help; I'm not sure what you meant by saying that you want to "calculate the average two columns" - is it (COLUMN_1 + COLUMN_2) / 2 or AVG(COLUMN_1) and AVG(COLUMN_2)?

Anyway, the principle is the same - check whether column contains a number and do the calculation. Otherwise, do nothing. For example:

SQL> with test (col1, col2) as
  2    (select 'a2' , 'ccc' from dual union all
  3     select '100', '200' from dual union all
  4     select '15' , 'xx'  from dual
  5    )
  6  select col1,
  7         col2,
  8         case when regexp_like(col1, '^\d+$') and regexp_like(col2, '^\d+$') then
  9                   (to_number(col1) + to_number(col2)) / 2
 10              else null
 11         end average
 12  from test;

COL COL    AVERAGE
--- --- ----------
a2  ccc
100 200        150
15  xx

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

If you want the overall average within each column, then:

select avg(case when col1 like '^\d+$' then to_number(col1) end) as avg_col1,
       avg(case when col2 like '^\d+$' then to_number(col2) end) as avg_col2
from t;

avg() ignores NULL values.

If you want the average within a row, then avg() is not appropriate (it is an aggregation function). In that case:

select (case when col1 like '^\d+$' and col2 like '^\d+$'
             then ( to_number(col1) + to_number(col2) ) / 2
             when col1 like '^\d+$'
             then to_number(col2)
             when col2 like '^\d+$'
             then to_number(col1)
        end) as avg_col1_2
from t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786