title: How to get all the sum in aggregate function?
body: <p>Here's some sample data:</p>
  
  <pre><code>dat="x1 x2 x3 x4 x5
  1   C  1 16 NA 16
  2   A  1 16 16 NA
  3   A  1 16 16 NA
  4   A  4 64 64 NA
  5   C  4 64 NA 64
  6   A  1 16 16 NA
  7   A  1 16 16 NA
  8   A  1 16 16 NA
  9   B  4 64 32 32
  10  A  3 48 48 NA
  11  B  4 64 32 32
  12  B  3 48 32 16"
  
  data&lt;-read.table(text=dat,header=TRUE)   
  aggregate(cbind(x2,x3,x4,x5)~x1, FUN=sum, data=data)   
   x1 x2  x3 x4 x5   
  1  B 11 176 96 8   
  </code></pre>
  
  <p>How do I get the sum of <code>A</code> and <code>C</code> <em>as well</em> in <code>x1</code>? </p>
  
  <pre><code> aggregate(.~x1, FUN=sum, data=data, na.action = na.omit)  
     x1 x2  x3 x4 x5
   1  B 11 176 96 80 
  </code></pre>
  
  <p>When I use <code>sqldf</code>:</p>
  
  <pre><code>library("sqldf")
  sqldf("select sum(x2),sum(x3),sum(x4),sum(x5) from data group by x1")
    sum(x2) sum(x3) sum(x4) sum(x5)
  1      12     192     192    &lt;NA&gt;
  2      11     176      96      80
  3       5      80      NA      80
  </code></pre>
  
  <p>Why do I get <code>&lt;NA&gt;</code> in the first line, but <code>NA</code> in the third line ?
  What is the differences between them? Why do I get the <code>&lt;NA&gt;</code>? there is no <code>&lt;NA&gt;</code> in data! </p>
  
  <pre><code>str(data)
  'data.frame':   12 obs. of  5 variables:
   $ x1: Factor w/ 3 levels "A","B","C": 3 1 1 1 3 1 1 1 2 1 ...
   $ x2: int  1 1 1 4 4 1 1 1 4 3 ...
   $ x3: int  16 16 16 64 64 16 16 16 64 48 ...
   $ x4: int  NA 16 16 64 NA 16 16 16 32 48 ...
   $ x5: int  16 NA NA NA 64 NA NA NA 32 NA ...
  </code></pre>
  
  <p>The sqldf problem remains here, why <code>sum(x4)</code> gets <code>NA</code>, on the contrary <code>sum(x5)</code> gets <code>&lt;NA&gt;</code>?</p>
  
  <p>I can prove that all <code>NA</code> both in  x4 and  x5 is the same this way:  </p>
  
  <pre><code>data[is.na(data)] &lt;- 0     
  
  &gt; data
     x1 x2 x3 x4 x5
  1   C  1 16  0 16
  2   A  1 16 16  0
  3   A  1 16 16  0
  4   A  4 64 64  0
  5   C  4 64  0 64
  6   A  1 16 16  0
  7   A  1 16 16  0
  8   A  1 16 16  0
  9   B  4 64 32 32
  10  A  3 48 48  0
  11  B  4 64 32 32
  12  B  3 48 32 16
  </code></pre>
  
  <p>So the fact that sqldf treats <code>sum(x4)</code> and <code>sum(x5)</code> differently is so strange that I think there is a logical mess in sqldf. It can be reproduced in other pc. Please do first and then have the discussion go on.</p>