title: Sum for multiple date ranges in a single query?
body: <p>I have the following query:</p>
  
  <pre><code>SELECT 
     SUM("balance_transactions"."fee") AS sum_id 
     FROM "balance_transactions" 
     JOIN charges ON balance_transactions.source = charges.balance_id 
     WHERE "balance_transactions"."account_id" = 6 
        AND (balance_transactions.type = 'charge' 
        AND charges.refunded = false 
        AND charges.invoice IS NOT NULL) 
        AND ("balance_transactions"."created" BETWEEN '2013-12-20' AND '2014-01-19');
  </code></pre>
  
  <p>What that does is adds up all the "fees" that occurred between those two dates. Great. Works fine.</p>
  
  <p>The problem is that I almost always need those fees for hundreds of date ranges at a time, which amounts to me running that same query hundreds of times. Not efficient. </p>
  
  <p>But is there some way to condense this into a single query for all the date ranges?</p>
  
  <p>For instance, I'd be calling <code>SUM</code> for a series of ranges like this:</p>
  
  <pre><code>2013-12-20 to 2014-01-19
  2013-12-21 to 2014-01-20
  2013-12-22 to 2014-01-21
  2013-12-23 to 2014-01-22
  2013-12-24 to 2014-01-23
  ...so on and so on
  </code></pre>
  
  <p>I need to output the sum of fees collected in each date range (and ultimately need that in an array).</p>
  
  <p>So, any ideas on a way to handle that and reduce database transactions?</p>
  
  <p>FWIW, this is on Postgres inside a Rails app.</p>