I've got a database which I'm looping through. Grouping results by year with a summary of spend then placing it all into an array

I've done the first level of my array like below. Setting my $data variable and adding first layer information to it.

My question is within my foreach loop I want to run a further query and do the same thing for monthly totals and then again within each month for

So my data will be

Year Summary
Month Summary
Month Details
Year Summary
Month Summary
Month Details
Year Summary
Month Summary
Month Details
How can I add an array into my array object

$data = [];
foreach ($getRows as $row){

$data[$YearCalc] = ['Year' => $row['YearCalc'], 'Credit' => $row['Credit'], 'Debit' => $row['Debit']];

}

var_dump(array_values($data));

    Doing a query inside a loop is a code smell already; the DBMS is fully capable of grouping results and totalling them in a single query. Look at the documentation for your DBMS to see what it has to say on the subject of grouping sets (e.g., GROUP BY clauses may have a CUBE or ROLLUP subclause).

    If that's too difficult to think through, consider selecting the fine-grained data and then make the various aggregate calculations in the application.

      Right, so All my months are just numbers 1,2,3,.... 54,55,56,57

      So I'm dividing that by 12 and flooring it to get the year number on the fly. So floor(28 / 12) = 2. Therefore year 2.

      It's just the way the data comes in.

      I want to now query a summary by year as a totals. Months as totals and finally list all data for each month.

      Any ideas

        It does, you can't use rollup as my "years" are a calculation.

          I don't have any trouble:

          Select
	Sum(quantity), Extract(month from orderdate) as month, Extract(year from orderdate) as year
From orderlines
Group BY Cube (month, year)

          (Obviously I don't have your database, but that's still grouping on the result of a calculation)

            Write a Reply...