title: Data Grouping in R (Akin to Summation with a WHERE clause in SQL)
body: <p>I am having a challenge grouping data in R using the <b>aggregate</b> command. I can do this with SQL relatively easily, but I am not able the get the same effect with R.</p>
  
  <p>As an example, here is a test set of data, that in SQL provides the essence of what I am looking for:</p>
  
  <pre><code>create table #data(v1 varchar(4), v2 int, v3 int, v4 int,v5 int)
  insert #data(v1, v2, v3, v4, v5) values(8000, 3, 8, 7, 11)
  insert #data(v1, v2, v3, v4, v5) values(8001, 4, 9, 8, 12)
  insert #data(v1, v2, v3, v4, v5) values(8002, 5, 10, 9, 13)
  insert #data(v1, v2, v3, v4, v5) values(8003, 6, 11, 7, 14)
  insert #data(v1, v2, v3, v4, v5) values(8000, 7, 12, 8, 11)
  insert #data(v1, v2, v3, v4, v5) values(8001, 3, 13, 9, 12)
  insert #data(v1, v2, v3, v4, v5) values(8002, 4, 14, 7, 13)
  insert #data(v1, v2, v3, v4, v5) values(8003, 5, 8, 8, 14)
  insert #data(v1, v2, v3, v4, v5) values(8000, 6, 9, 9, 11)
  insert #data(v1, v2, v3, v4, v5) values(8001, 7, 10, 7, 12)
  insert #data(v1, v2, v3, v4, v5) values(8002, 3, 11, 8, 13)
  insert #data(v1, v2, v3, v4, v5) values(8003, 4, 12, 9, 14)
  insert #data(v1, v2, v3, v4, v5) values(8000, 5, 13, 7, 11)
  insert #data(v1, v2, v3, v4, v5) values(8001, 6, 14, 8, 12)
  insert #data(v1, v2, v3, v4, v5) values(8002, 7, 8, 9, 13)
  insert #data(v1, v2, v3, v4, v5) values(8003, 3, 9, 7, 14)
  insert #data(v1, v2, v3, v4, v5) values(8000, 4, 10, 8, 11)
  insert #data(v1, v2, v3, v4, v5) values(8001, 5, 11, 9, 12)
  insert #data(v1, v2, v3, v4, v5) values(8002, 6, 12, 7, 13)
  insert #data(v1, v2, v3, v4, v5) values(8003, 7, 13, 8, 14)
  insert #data(v1, v2, v3, v4, v5) values(8000, 3, 14, 9, 11)
  insert #data(v1, v2, v3, v4, v5) values(8001, 4, 8, 7, 12)
  insert #data(v1, v2, v3, v4, v5) values(8002, 5, 9, 8, 13)
  insert #data(v1, v2, v3, v4, v5) values(8003, 6, 10, 9, 14)
  insert #data(v1, v2, v3, v4, v5) values(8000, 7, 11, 7, 11)
  insert #data(v1, v2, v3, v4, v5) values(8001, 3, 12, 8, 12)
  insert #data(v1, v2, v3, v4, v5) values(8002, 4, 13, 9, 13)
  insert #data(v1, v2, v3, v4, v5) values(8003, 5, 14, 7, 14)
  
  select * from #data
  
  select v1, sum(v2)
  from #data
      where v4 &lt;= v3 and v5 &gt; v3
  group by v1
  
  drop table #data
  </code></pre>
  
  <p>In R, I have tried using <b>aggregate</b> with a <b>subset</b> command, <b>aggregate</b> with an inline function(x)... and still have not been able to coalesce the data in the way I was hoping for in a purely 'R' manner. I am aware of the SQL library to allow SQL statements to be transacted upon a data structure, but I am looking to avoid that since I am pulling the data from a database in the first place, and if I need to resort to that, I might as well write the SQL to do it. I am looking for a purist R way of doing it. Perhaps it is a bit idealistic, but that is the hope and the dream.</p>
  
  <p>Something to note with the clause is it is something of an exclusionary clause summing the data NOT included by the V2 variable in the current row. If it were otherwise, this would be a fairly simple problem for me, but I am not grepping the passing the current line under scrutiny's values to a function against the larger data set, or if another solution altogether would be better.</p>
  
  <p>Thanks in advance for the help.</p>
  
  <p>Here is the R code to generate the test data:</p>
  
  <pre><code>m.data &lt;- as.data.frame(
          cbind(8000:8003, 3:7, 8:14, 7:9, 11:14, 1:28),
          row.names=NULL
  );
  </code></pre>