title: SQL's `case when ...` code conversion using data.table package in R
body: <p>I am attempting to convert SQL code to R code. However, the data is around 35 million records with 200 columns each. So the best choice I could find is data.table package.</p>
  
  <p>Here is the problem. 
  In the SQL code I am able to perform an operation such as this,</p>
  
  <p></p>
  
  <pre><code>select order_date,sum(case when item in ("D","C","B") then col4 end)as col1
  sum(case when item not in ("Z","X","Y") then col4 end) as col2 
  from datatable 
  where col3 &lt;25
  group by order_date;
  </code></pre>
  
  <p>What the above query allows me to group by each date. I am unable to duplicate it in data.table. My attempts are as follows.</p>
  
  <p></p>
  
  <pre><code>grp1&lt;- c("D","C","B")
  grp2&lt;- c("Z","X","Y")
  d1 &lt;- dat[item %in% grp1,.(col1 = sum(col4,na.rm = TRUE),by = Order_Date]
  d2 &lt;- dat[item %in% grp2,.(col2 = sum(col4,na.rm = TRUE),by = Order_Date]
  d3 &lt;- data.table(d1,d2)
  </code></pre>
  
  <p>Now, since it <code>subsets</code> initially my grouping is different in both <code>d1</code> and <code>d2</code></p>