How to detect NULL rows in PostgreSQL sum()
<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>