title: How to detect NULL rows in PostgreSQL sum()
body: <p>I want to aggregate the sum of a column, while tracking the presence of NULL values which signal an error condition. E.g., take the table numbers:</p>
  
  <pre><code># select * from numbers;
    n   |  l  
  ------+-----
      1 | foo
      2 | foo
   NULL | bar
      4 | bar
  </code></pre>
  
  <p>Given a label <code>l</code>, I want to compute the sum of numbers <code>n</code> with that label, provided there are no <code>NULL</code> values. Ideally, for a label without any rows, the sum would be 0. So I'm looking for some query <code>q</code> such that
  <code>q('foo') = 3</code>, <code>q('baz') = 0</code> and <code>q('bar')</code> somehow signals an error, e.g. by returning <code>NULL</code>.</p>
  
  <p>I started with the <code>sum()</code> <a href="https://www.postgresql.org/docs/9.4/static/functions-aggregate.html" rel="noreferrer">aggregate function</a>, but that converts <code>NULL</code> rows to 0. One solution would be a variant that returns <code>NULL</code> provided there are any <code>NULL</code> values.</p>
  
  <p><code>sum()</code> gives</p>
  
  <pre><code># select sum(n) from numbers where l = 'bar';
   sum 
  -----
     4
  </code></pre>
  
  <p>but I'd rather have <code>sumnull()</code> with</p>
  
  <pre><code># select sumnull(n) from numbers where l = 'bar';
   sumnull 
  ---------
    NULL
  </code></pre>
  
  <p>The best solution I've found so far is to also count non-NULL rows and compare to the total count:</p>
  
  <pre><code># select sum(n), count(*), count(n) as notnull from numbers;
   sum | count | notnull 
  -----+-------+---------
     7 |     4 |       3
  </code></pre>
  
  <p>Then if <code>count</code> is unequal to <code>notnull</code>, I know the result is not valid.</p>