CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3postgresql-9.2
Taz
Taz

Reputation: 113

Sum values of a row after giving a numeric value to a varchar

I have two tables, table_a and table_b as below.

Table table_a:

  a_id |columna |varchar1|varchar2|varchar3
----------------------------------
|   1  |  a     | Medium |  High  | n/a 
|   2  |  b     | Low    |  n/a   | n/a
|   3  |  c     | Medium |  Low   | High

In table_a each varchar value is given a numerical value: High as 1, Medium as 2 and Low as 3.

Table table_b:

  b_id |columna_fk|average
--------------------------
|   1  |  a       |
|   1  |  b       |
|   3  |  c       |

How to get average values (ignoring n/a) of row and update average column in table_b?

For example see table below:

  b_id |columna_fk|average
--------------------------
|   1  |  a       | 1.5
|   1  |  b       | 3
|   3  |  c       | 2

Upvotes: 0

Views: 61

Answers (2)

Taz
Taz

Reputation: 113

Thanks for you answer, I made few changes and it's working for me. below is my change.

update b
set average = 
(case when 
((case varchar1 when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 else 0 end) +
 (case varchar2 when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 else 0 end) +
 (case varchar3 when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 else 0 end)) <> 0 
then
((case varchar1 when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 else 0 end) +
 (case varchar2 when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 else 0 end) +
 (case varchar3 when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 else 0 end)) 
/
((case varchar1 when 'n/a' then 0 else 1 end) +
 (case varchar2 when 'n/a' then 0 else 1 end) +
 (case varchar3 when 'n/a' then 0 else 1 end))
else null end)
from a where a.columna = b.columna_fk;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269733

One method is brute force:

update b
    set average = ((case varchar1 when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 end) +
                   (case varchar2 when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 end) +
                   (case varchar3 when 'High' then 1 when 'Medium' then 2 when 'Low' then 3 end)
                  ) /
                  nullif((case varchar1 when 'n/a' then 0 else 1 end) +
                         (case varchar2 when 'n/a' then 0 else 1 end) +
                         (case varchar3 when 'n/a' then 0 else 1 end)
                        )
    from a
    where a.columna = b.columna_fk;

Upvotes: 1

Related Questions