Summing up array values using multiple conditions

Question

I am trying to get the sum of all "amount" elements within my array based on customer and date range. Im wanting the output array (final result) to look something like this ...

Array
(
[0] => stdClass Object
    (
        [company_name] => Yeung's Architects
        [customer_id] => 3
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 1543.64
        [account_id] => 75
    )

[1] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2177.27
        [account_id] => 75
    )

[2] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 295.45
        [account_id] => 75
    )
 )

This is the sql call that I am using ...

SELECT c.company_name
     , i.customer_id
     , a.name
     , i.due_date
     , li.amount
     , li.account_id 
  FROM invoices i 
  JOIN customers c 
    ON c.customer_id = i.customer_id
  JOIN invoices_line_items li 
    ON li.guid = i.guid 
   AND li.invoice_id = i.invoice_id
  JOIN accounts a 
    ON a.guid = li.guid 
   AND a.account_id = li.account_id
 WHERE i.guid = ? 
   AND i.date >= "'.$firstOfThisMonth.'" ',array($guid));

The resulting array from the sql call to start working with...

Array
(
[0] => stdClass Object
    (
        [company_name] => Yeung's Architects
        [customer_id] => 3
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 1362.73
        [account_id] => 75
    )

[1] => stdClass Object
    (
        [company_name] => Yeung's Architects
        [customer_id] => 3
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 180.91
        [account_id] => 75
    )

[2] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 177.27
        [account_id] => 75
    )

[3] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 159.09
        [account_id] => 75
    )

[4] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 136.36
        [account_id] => 75
    )

[5] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Services
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2000.00
        [account_id] => 1
    )

   )

The code I am currently using to try and sum all of the amounts based on customer ID and the month of the invoice.

            $output = array($data[0]); $c = 1;
            foreach ($data as $key => $value) {
                if ($key > 0) {

                    if (in_array($value->customer_id,array_column($output, 'customer_id'))) {

                        $valueDate = substr($value->due_date,0,7);
                        foreach ($output as $k => $v) {
                            if ($v->customer_id == $value->customer_id) {
                                $vDate = substr($v->due_date,0,7);
                                if ($vDate == $valueDate) {
                                    $output[$k]->amount = $output[$k]->amount + $value->amount;
                                } else {
                                    $output[$c] = $value;
                                    $c++;
                                }

                            }
                        }

                    } else {
                        $output[$c] = $value;
                        $c++;
                    }

                }
            }

            return $output;

This is my final results from the above mentioned code...

Array
(
[0] => stdClass Object
    (
        [company_name] => Yeung's Architects
        [customer_id] => 3
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 1543.64
        [account_id] => 75
    )

[1] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2177.27
        [account_id] => 75
    )

[2] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 295.45
        [account_id] => 75
    )

[3] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Billable Expenses Income
        [due_date] => 2019-05-19 00:00:00
        [amount] => 136.36
        [account_id] => 75
    )

[4] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Services
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2000.00
        [account_id] => 1
    )

[5] => stdClass Object
    (
        [company_name] => Clement's Cleaners
        [customer_id] => 8
        [name] => Services
        [due_date] => 2019-04-19 00:00:00
        [amount] => 2000.00
        [account_id] => 1
    )

   )

The first 3 keys are the correct results but then there are 3 extra keys added which Im not sure how they are being added. My brain is now broken, please may someone help me out, any help would be greatly appreciated.

Not wanting to use SQL aggregate functions? Databases are great at this. — ficuscr
– ficuscr, Commented Mar 20, 2019 at 20:48
I thought about doing something like that but tbh my SQL is very basic. — 5abre
– 5abre, Commented Mar 20, 2019 at 20:53

ficuscr · Accepted Answer · 2019-03-20 21:03:04Z

1

So if I am following you just want to group this by company_name or is there another dimension? Doing this in SQL is actually very straightforward.

Query using an aggregate function, sum, and grouping by the company field. You can see below very little changed.

SELECT c.company_name
     , i.customer_id
     , a.name
     , i.due_date
     , SUM(li.amount)
     , AVG(li.amount)
     , li.account_id 
  FROM invoices i 
  JOIN customers c 
    ON c.customer_id = i.customer_id
  JOIN invoices_line_items li 
    ON li.guid = i.guid 
   AND li.invoice_id = i.invoice_id
  JOIN accounts a 
    ON a.guid = li.guid 
   AND a.account_id = li.account_id
 WHERE i.guid = ? 
   AND i.date BETWEEN :startDate AND :endDate
 GROUP BY i.customer_id

More info can be found here: https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html and here https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

You could add a second GROUP BY (as many as you want really) if needed. Let's say you want one query that gets "sum by customer, by month". You would do something like...

 GROUP BY i.customer_id, DATE_FORMAT(i.due_date, '%Y-%m')

The WITH ROLLUP can be useful in debugging.

edited Mar 20, 2019 at 21:03

answered Mar 20, 2019 at 20:55

ficuscr

7,0932 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

5abre Over a year ago

The other dimension is the date range, group by due_date... Thank you very much for putting me on the right track. I have resolved this now using your method. Thank you again.

Paul Spiegel Over a year ago

Please read: dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Collectives™ on Stack Overflow

Summing up array values using multiple conditions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related