CODEWITHSUNDEEP
phpmysqlcodeigniter
David Savage
David Savage

Reputation: 1552

Conditional Sum in mySQL?

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.

Here's the tables I'm dealing with (showing only relevant fields):

Organizations

FundingCycles

AdministrativeRequests

CapitalRequests

EventRequests

So, here's the complicated part. I need to create sums for each organization of the AmountRequested, AmountFunded, (those two seem decently easy, although not sure about summing across multiple tables), and a calculated field AmountAfterCutback from requests that are in all request tables (AdministrativeRequests, CapitalRequests, EventRequests).

AmountAfterCutback is equal to: AmountFunded - (AmountFunded * Cutback).

That might be simple enough if the cutback was just one value. However, the Cutback comes from the FundingCycles 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 CycleID check and see what the cutback for that request is in the FundingCycles table.

By default, Cutback would be equal to NonUnityCutBack. However, if the request is a Unity request (EventRequests only, UnityReq == 1), the cutback is equal to UnityCutBack. It gets even MORE challenging, however, in that a cutback can be applied individually to each request, overriding the unity or nonunity cutbacks (if PerOff is > 0).

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!

function calculate_public_records()
{
      $total_req_total = 0;
      $total_funded_total = 0;
      $total_cutback_total = 0;

      $organizations = array();

      foreach($this->organizations as $org)
      {
          $id = $org['OrgID'];
          $org_req_total = 0;
          $org_funded_total = 0;
          $org_cutback_total = 0;

          $cycles = array();

          foreach($this->cycles as $cycle)
          {
                $cycle_req_total = 0;
                $cycle_funded_total = 0;
                $cycle_cutback_total = 0;

                $cycle_requests = array();

                foreach($this->request_types as $type)
                {
                    $reqs = $this->funding_request_model->getRequests($type, $cycle['CycleID'], $id);
                    foreach($reqs as $r)
                    {
                        $cutback = $cycle['NonUnityCutBack'];
                        if ($type == "Event") $cutback = ($r->UnityReq == 1) ? $cycle['UnityCutBack'] : $cutback;
                        $cutback = ($r->PerOff != 0) ? $r->PerOff : $cutback;

                        $request = array();
                        $request['id']        = $r->ReqID;
                        $request['type']      = $type;
                        $request['name']      = $r->Title;
                        $request['requested'] = number_format($r->RequestTotal, 2);
                        $request['funded']    = number_format($r->AmtFunded, 2);
                        $request['cutback']   = number_format(($r->AmtFunded - ($r->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' => $cycle['CycleName'], 'requests' => $cycle_requests, 'totals' => $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' => $org['OrgID'], 'name' => $org['Organization'], 'cycles' => $cycles, 'totals' => $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' => $organizations, 'totals' => $totals);

Calculation summary:

# For tables AdministrativeRequests, CapitalRequests & EventRequests, calculate:

    SUM(AmountRequested)
    SUM(AmountFunded)

# For tables AdministrativeRequests, CapitalRequests & EventRequests, calculate:

    AmountAfterCutback = AmountFunded - (AmountFunded * Cutback)

# Cutback calculation pseudocode

if( <table>.PerOff > 0 ) {
    Cutback = <table>.PerOff
} elseif ( <table> == EventRequests && EventRequests.UnityReq == 1 ) {
    Cutback = FundingCycles.UnityCutBack
} else {
    Cutback = FundingCycles.NonUnityCutBack
}

Upvotes: 4

Views: 1087

Answers (2)

Mike
Mike

Reputation: 21659

Here's my attempt - updated to use UNION ALL instead of UNION:

SELECT OrgID,
       SUM(AmountRequested),
       SUM(AmountFunded),
       SUM(AmountFunded - (
           AmountFunded * 
           IF( PerOff > 0,
               PerOff,
               IF( UnityReq = 1,
                   UnityCutBackAmount,
                   NonUnityCutBackAmount
               )
           )
       )) AS AmountAfterCutback
  FROM (
SELECT o.OrgID,
       ar.AmountRequested,
       ar.AmountFunded,
       ar.PerOff,
       null AS UnityReq,
       fc_ar.CycleID,
       fc_ar.NonUnityCutBackAmount,
       fc_ar.UnityCutBackAmount
  FROM Organizations o
  JOIN AdministrativeRequests ar
    ON ar.OrgID = o.OrgID
  JOIN FundingCycles fc_ar
    ON fc_ar.CycleID = ar.CycleID
 UNION ALL
SELECT o.OrgID,
       cr.AmountRequested,
       cr.AmountFunded,
       cr.PerOff,
       null AS UnityReq,
       fc_cr.CycleID,
       fc_cr.NonUnityCutBackAmount,
       fc_cr.UnityCutBackAmount
  FROM Organizations o
  JOIN CapitalRequests cr
    ON cr.OrgID = o.OrgID
  JOIN FundingCycles fc_cr
    ON fc_cr.CycleID = cr.CycleID
 UNION ALL
SELECT o.OrgID,
       er.AmountRequested,
       er.AmountFunded,
       er.PerOff,
       er.UnityReq,
       fc_er.CycleID,
       fc_er.NonUnityCutBackAmount,
       fc_er.UnityCutBackAmount
  FROM Organizations o
  JOIN EventRequests er
    ON er.OrgID = o.OrgID
  JOIN FundingCycles fc_er
    ON fc_er.CycleID = er.CycleID
) tmp
 GROUP BY OrgID;

Upvotes: 2

symcbean
symcbean

Reputation: 48367

Learn SQL. A quick read suggests the answer might be something like...

    SELECT orgid
    , SUM(requested) AS amountrequested
    , SUM(funded) AS amountfunded
    , SUM(IF(unityreq=1, unitycutback, nonunitycutback) * funded) 
           AS amountaftercutback
    FROM
    ( 
      SELECT cycleid, orgid, unityreq, 
        SUM(amountrequested) as requested, SUM(amountfunded) as funded
      FROM
        (
           (
           SELECT cycleid, orgid, AmountRequested, AmountFunded, 0 as unityreq
           FROM administrativerequests 
           )
           UNION
           (
           SELECT cycleid, orgid, AmountRequested, AmountFunded, 0 as unityreq
           FROM capitalrequests
           )
           UNION
           (
           SELECT cycleid, orgid, AmountRequested, AmountFunded, unityreq
           FROM eventrequests
           )
        ) ilv
      GROUP BY cycleid, orgid, unityreq
    ) ilv,
    fundingcycles fc
    WHERE fc.cycleid=ilv.cycleid
    GROUP BY orgid

Upvotes: 2

Related Questions