title: Conditional Sum in mySQL?
body: <p>I'm not very good at SQL, so I've been trying to just handle this through PHP and doing very basic mySQL queries, but apparently I keep running out of memory (in PHP) since I'm handling a lot of data (and doing tons of horrible nested foreach's). I'm hoping there might be a way to re-write my SQL to make this not happen.</p>
  
  <p>Here's the tables I'm dealing with (showing only relevant fields):</p>
  
  <p>Organizations</p>
  
  <ul>
  <li>OrgID</li>
  </ul>
  
  <p>FundingCycles</p>
  
  <ul>
  <li>CycleID</li>
  <li>NonUnityCutBackAmount</li>
  <li>UnityCutBackAmount</li>
  </ul>
  
  <p>AdministrativeRequests</p>
  
  <ul>
  <li>CycleID</li>
  <li>OrgID</li>
  <li>AmountRequested</li>
  <li>AmountFunded</li>
  <li>PerOff</li>
  </ul>
  
  <p>CapitalRequests</p>
  
  <ul>
  <li>CycleID</li>
  <li>OrgID</li>
  <li>AmountRequested</li>
  <li>AmountFunded</li>
  <li>PerOff</li>
  </ul>
  
  <p>EventRequests</p>
  
  <ul>
  <li>CycleID</li>
  <li>OrgID</li>
  <li>AmountRequested</li>
  <li>AmountFunded</li>
  <li>PerOff</li>
  <li>UnityReq</li>
  </ul>
  
  <p>So, here's the complicated part. I need to create sums for each organization of the <code>AmountRequested</code>, <code>AmountFunded</code>, (those two seem decently easy, although not sure about summing across multiple tables), and a calculated field <code>AmountAfterCutback</code> from requests that are in all request tables (<code>AdministrativeRequests</code>, <code>CapitalRequests</code>, <code>EventRequests</code>).</p>
  
  <p><code>AmountAfterCutback</code> is equal to: <code>AmountFunded - (AmountFunded * Cutback</code>).</p>
  
  <p>That might be simple enough if the cutback was just one value. However, the <code>Cutback</code> comes from the <code>FundingCycles</code> table. So I can't just straight sum and apply the cutback, I have to find out the cutback for each request, and to do that I have to look at each request, and then by its <code>CycleID</code> check and see what the cutback for that request is in the <code>FundingCycles</code> table.</p>
  
  <p>By default, <code>Cutback</code> would be equal to <code>NonUnityCutBack</code>. However, if the request is a <code>Unity</code> request (<code>EventRequests</code> only, <code>UnityReq == 1</code>), the cutback is equal to <code>UnityCutBack</code>. It gets even MORE challenging, however, in that a cutback can be applied individually to each request, overriding the unity or nonunity cutbacks (if <code>PerOff is &gt; 0</code>). </p>
  
  <p>So, somehow I have to sum every request an organization has made, and properly calculate the fields mentioned above. I'll show my solution below, which I'll warn is definitely not pretty, and at this point not even functional as it runs out of memory when trying to process it via PHP. I know there HAS to be a better way to do this. Some way to do a conditional calculation of each request so it can be summed in the query rather than my below clunky PHP solution? Any ideas? Thanks for anyone who's had the patience to even read this far, I know this is a PITA of a question. Any help is greatly appreciated!</p>
  
  <pre><code>function calculate_public_records()
  {
        $total_req_total = 0;
        $total_funded_total = 0;
        $total_cutback_total = 0;
  
        $organizations = array();
  
        foreach($this-&gt;organizations as $org)
        {
            $id = $org['OrgID'];
            $org_req_total = 0;
            $org_funded_total = 0;
            $org_cutback_total = 0;
  
            $cycles = array();
  
            foreach($this-&gt;cycles as $cycle)
            {
                  $cycle_req_total = 0;
                  $cycle_funded_total = 0;
                  $cycle_cutback_total = 0;
  
                  $cycle_requests = array();
  
                  foreach($this-&gt;request_types as $type)
                  {
                      $reqs = $this-&gt;funding_request_model-&gt;getRequests($type, $cycle['CycleID'], $id);
                      foreach($reqs as $r)
                      {
                          $cutback = $cycle['NonUnityCutBack'];
                          if ($type == "Event") $cutback = ($r-&gt;UnityReq == 1) ? $cycle['UnityCutBack'] : $cutback;
                          $cutback = ($r-&gt;PerOff != 0) ? $r-&gt;PerOff : $cutback;
  
                          $request = array();
                          $request['id']        = $r-&gt;ReqID;
                          $request['type']      = $type;
                          $request['name']      = $r-&gt;Title;
                          $request['requested'] = number_format($r-&gt;RequestTotal, 2);
                          $request['funded']    = number_format($r-&gt;AmtFunded, 2);
                          $request['cutback']   = number_format(($r-&gt;AmtFunded - ($r-&gt;AmtFunded * $cutback)), 2);
  
                          $cycle_req_total += $request['requested'];
                          $cycle_funded_total += $request['funded'];
                          $cycle_cutback_total += $request['cutback'];
  
                          $cycle_requests[] = $request;
                      }
  
                  }
  
                  $cycle_totals = array();
                  $cycle_totals['requested'] = number_format($cycle_req_total, 2);
                  $cycle_totals['funded'] = number_format($cycle_funded_total, 2);
                  $cycle_totals['cutback'] = number_format($cycle_cutback_total, 2);
  
                  $org_req_total += $cycle_req_total;
                  $org_funded_total += $cycle_funded_total;
                  $org_cutback_total += $cycle_cutback_total;
  
                  $cycles[] = array('name' =&gt; $cycle['CycleName'], 'requests' =&gt; $cycle_requests, 'totals' =&gt; $cycle_totals);
            }
  
            $org_totals = array();
            $org_totals['requested'] = number_format($org_req_total, 2);
            $org_totals['funded'] = number_format($org_funded_total, 2);
            $org_totals['cutback'] = number_format($org_cutback_total, 2);
  
            $total_req_total += $org_req_total;
            $total_funded_total += $org_funded_total;
            $total_cutback_total += $org_cutback_total;
  
            $organizations[] = array('id' =&gt; $org['OrgID'], 'name' =&gt; $org['Organization'], 'cycles' =&gt; $cycles, 'totals' =&gt; $org_totals);
        }
  
        $totals = array();
        $totals['requested'] = number_format($total_req_total, 2);
        $totals['funded'] = number_format($total_funded_total, 2);
        $totals['cutback'] = number_format($total_cutback_total, 2);
  
        return array('organizations' =&gt; $organizations, 'totals' =&gt; $totals);
  </code></pre>
  
  <p><strong>Calculation summary:</strong></p>
  
  <pre><code># For tables AdministrativeRequests, CapitalRequests &amp; EventRequests, calculate:
  
      SUM(AmountRequested)
      SUM(AmountFunded)
  
  # For tables AdministrativeRequests, CapitalRequests &amp; EventRequests, calculate:
  
      AmountAfterCutback = AmountFunded - (AmountFunded * Cutback)
  
  # Cutback calculation pseudocode
  
  if( &lt;table&gt;.PerOff &gt; 0 ) {
      Cutback = &lt;table&gt;.PerOff
  } elseif ( &lt;table&gt; == EventRequests &amp;&amp; EventRequests.UnityReq == 1 ) {
      Cutback = FundingCycles.UnityCutBack
  } else {
      Cutback = FundingCycles.NonUnityCutBack
  }
  </code></pre>