title: Counting occurrences of a value in multiple columns - postgres
body: <p>I have a table called <code>fixtures</code> (I have simplified for this example) and would like to populate the last two columns (*_plus_mc_per) with the percentage of times occurred for each number with a query run against the <code>mc_*</code> columns. It would look like this as an example </p>
  
  <pre><code>#mc = Match Corner # mc_per = Match Corner Percentage
  | mc_0 | mc_1 | mc_3 | mc_4 | match_count | one_plus_mc_per | two_plus_mc_per |
  | 1    | 4    | 3    | null | 3           | 100             |  66             |
  </code></pre>
  
  <p>At the point where I run my query it looks like</p>
  
  <pre><code> #mc = Match Corner # mc_per = Match Corner Percentage
  | mc_0 | mc_1 | mc_3 | mc_4 | match_count | one_plus_mc_per | two_plus_mc_per |
  | 1    | 4    | 3    | null | 3           | null            |  null           |
  </code></pre>
  
  <p>So starting with the query for <code>one_plus_mc_per</code> I can do this</p>
  
  <pre><code>SELECT COUNT(*) FROM fixtures WHERE coalesce(mc_0,0) &gt;= 1 AND id = 182; 
  # Using coalesce for dealing with null, will return a 0 if value null
  </code></pre>
  
  <p>This returns</p>
  
  <pre><code>| count |
  | 1     |
  </code></pre>
  
  <p>If I run this query on each column individually the results returned would be</p>
  
  <pre><code>| count | count | count | count | 
  | 1     | 1     | 1     | 0     |
  </code></pre>
  
  <p>Thus enabling me to add all the column values up and divide by my match count. This makes sense (and I thank dmfay for getting me to think about his suggestion in a previous question)</p>
  
  <p>My problem is I can't run this query 4 times for example as that is very ineffective. My SQL fu is not strong and was looking for a way to do this in one call to the database, enabling me to then take that percentage value and update the percentage column</p>
  
  <p>Thanks </p>