What would be the most performant solution in this case of fetching and summing a lot of data?

I have the following db table structure:

division   |  department |  unit   |    subunit   |   worker_no    |  money_data1   |    money_data2 | ...

So the table is filled with rows with information about workers and some money related data.

I need to display a hierarchy table that goes "top to bottom" in summing the money data. The table in the frontend should be structured like so: The columns would be the sum of all the different money_datas, with rows consiting of all sub-units, from the division, to each individual worker's data. (So you can go inside each level)

Here is an example:

                                                  |money_data1|money_data2|...
--------------------------------------------------|-----------|-----------|
division1_sum                                     |           |           | 
                                                  |           |           | 
division1_department1_sum                         |           |           |           
                                                  |           |           |
division1_1_department1_subunit1_sum              |           |           |
                                                  |           |           |
division1_1_department1_unit1_subunit1_sum        |           |           |
                                                  |           |           |
division1_1_department1_unit1_subunit1_worker1_sum|           |           |
                                                  |           |           |
division1_1_department1_unit1_subunit1_worker2_sum|           |           |

In this example I went down all the way from some division to displaying each of the workers consisting of it. (And of course this division can have many more departments and many more units etc.. and there are a few divisions as well)

Should I:

Get all the data of individual workers then use PHP to sum each level
Do something with SQL? Maybe use SQL to sum the main levels and then for individual workers just fetch the data sum with PHP?

SilenceBringer

3 years ago

Level 55

@ligonsker for sure you need to do it at sql level, like:

select
	sum(if(division = 1, money_data1, 0)) as division1_money_data1,
	sum(if(division = 1 and department = 1, money_data1, 0) as division1_department1_money_data1,
	...
from <table_name>

firstly you'll need to fetch distinct values for division/department/unit/subunit/worker_no to create this query in php using loop. I think it will efficient way. Maybe someone will suggest something better

1 like

Please or to participate in this conversation.