system design help for a total value aggregate between categories months. Effeciency and data handing

Overview I want to find out about a more efficient way of handing this sittuation, after implementing the solution I have now, I do think it is terribly inefficient. And since I like to learn about these design patterns, I appreciate any better solutions to the problem.

Its not that relevant to the problem, but the language is php and database - mysql.

Problem I got 2 tables, one transactions and one transactions categories, the objective is to display summary of transaction totals per category, per month for a number of months.

Solution 1 So far I got 2 solutions and have implemented one, looking back at it, it seems a terrible solution, so what I did is this (pseudo code):

$categories = Categories::all();
foreach($category) {
 $category->getMonthSummary(0)
 $category->getMonthSummary(-1)
 $category->getMonthSummary(-2)
 $category->getMonthSummary(-3)
 $category->getMonthSummary(-4)
}

This is the gist of what I do, getMonthSummary takes an offset from which it works out what month I wish to retrieve the data for and it retrieves related transactions that happen in this month (where between dates)

This means that at minimum for each category I will have 5 separate queries. If I have 5 categories - this is already 25.

This seems to be to be highly terrible... Granted I am doing this just for fun and will be the sole user of the system... but I can't live with the inefficiency... So have been considering the following approach

$datagrid = array();
$transactions::whereBetween('date', [min, max]);
foreach($transaction) {
 // Fill out the array based on which month/category transaction ends up in
}

The table I am looking for is something like this (for the sake of example, I am after last 5 months, but its not really important as it could be dynamic filters):

| category | month -4 | month -3 | month -2 | month -1 | month |
| a        | xxx      | xxx      |  xxx     | xxx      | xxx   | 
| b        | xxx      | xxx      |  xxx     | xxx      | xxx   | 
| c        | xxx      | xxx      |  xxx     | xxx      | xxx   | 

Here xxx is the sum of all transactions, and few other randomly useful to me information, ie I need to know the sum and then do some maths that seems to make sense to me for each month to see how things are going.

---

It would be nice if someone who knows a better system design to enlgihten me about this problem. Surely there must be a more efficient way of achieving just that then either of the two solutions