1

I need to get the sum of each column of my table. So i used select sum(col1),col2 etc.

If the sum is null, i need to get 0, else the value of the sum. So I used "select case when sum(col1) is null then 0 else sum(col1) end as sum_col1".

I have around 40 such columns in my table. Do i need to write " case when sum(col n) then..." 40 times in my query?

Im working on oracle 9 g. Thanks

schan
  • 51
  • 6
  • 2
    Side note: `NVL(SUM(...), 0)` should save you some typing. – Jeroen Mostert Nov 19 '14 at 12:50
  • @jeroen: if i want the result to be an empty string instead of 0, can i give nvl(col1, '')? When i try this, i again get 'null'. Can nvl return an empty string? – schan Nov 20 '14 at 07:15
  • I'm not very well versed in Oracle, so please make this a separate question if your question isn't already answered by reading [the NVL documentation](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm) and [this question](http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null/). – Jeroen Mostert Nov 20 '14 at 07:34

1 Answers1

2

I have around 40 such columns in my table. Do i need to write " case when sum(col n) then..." 40 times in my query?

Short answer: Yes.

Longer answer: You might be able to use some kind of dynamic SQL to generate the statement automatically from the column metadata. But it might not be worth the trouble, as you can often just as easily copy-paste the statement in your query editor. All things considered, having a table with 40 columns that you need to sum, indicates a bad data model design. When working with a badly designed data model, you pay the price at query time...

Dan
  • 10,480
  • 23
  • 49