CODEWITHSUNDEEP
sqloracle-database
Omri
Omri

Reputation: 1656

Count how many columns are bigger than 0

I have a table that has some numeric columns. I need to count how many have values that are greater than 0, and to add it as a new column.

For example: The current table is:

A | B | C | D | E |
2 | 4 | 0 | 8 | 0 |
0 | 0 | 0 | 0 | 1 |

The output would be:

A | B | C | D | E | "New column"
2 | 4 | 0 | 8 | 0 | 3
0 | 0 | 0 | 0 | 1 | 1

Upvotes: 0

Views: 137

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can do this with the brute force method:

select t.*,
       ((case when a > 0 then 1 else 0 end) +
        (case when b > 0 then 1 else 0 end) +
        (case when c > 0 then 1 else 0 end) +
        (case when d > 0 then 1 else 0 end) +
        (case when e > 0 then 1 else 0 end)
       ) as NewColumn
from currenttable t;

If you actually want a new column in the table, then you should do:

  • alter the table to add the new column
  • run an update statement similar to the above select
  • consider a trigger to keep the value up-to-date

EDIT:

Alex's comment is worth mentioning. In the more recent versions of Oracle, you can add a virtual column which would do this calculation as part of the table definition itself. Virtual columns are definitely a better way to solve this problem than adding a new non-virtual column to the table.

Upvotes: 2

Related Questions