title: How can I count the number of zeros in all columns for a table in oracle sql
body: <p>Suppose you have a database table named db_table1 with hundreds of columns and most of them contain lots of 0's.</p>
  <p>I'm trying to count zeros for each column and divide it by the length of column so I can see the ratio of zeros in each column.</p>
  <p>The below code gives me the ratio of nulls in each column. But I couldn't revise it to count 0's instead of nulls</p>
  <p>Any help will be appreciated</p>
  <pre><code>SELECT column_name, (num_nulls/ (SELECT COUNT(*) FROM db_table)*100)
  FROM all_tab_columns 
  WHERE table_name='table’
  </code></pre>
  <p>Sample data:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>col_1</th>
  <th>col_2</th>
  <th>col_3</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0.000</td>
  <td>0.000</td>
  <td>0.000</td>
  </tr>
  <tr>
  <td>0.000</td>
  <td>0.000</td>
  <td>14.857</td>
  </tr>
  <tr>
  <td>3.548</td>
  <td>0.000</td>
  <td>0.000</td>
  </tr>
  <tr>
  <td>0.000</td>
  <td>0.000</td>
  <td>0.000</td>
  </tr>
  <tr>
  <td>0.000</td>
  <td>0.000</td>
  <td>0.000</td>
  </tr>
  <tr>
  <td>0.000</td>
  <td>11.586</td>
  <td>0.000</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Expected Output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>All_Columns</th>
  <th>Ratio_of_Zeros</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>col_1</td>
  <td>65.5</td>
  </tr>
  <tr>
  <td>col_2</td>
  <td>73.5</td>
  </tr>
  <tr>
  <td>col_3</td>
  <td>48.6</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Briefly I need the total number of zeros in every column divided by total row number which gives me the percent or ratio of zeros in each column so I can treat zeros like null and eliminate the column if it has more than 60 percent zeros lets say...</p>