mysql - Multiple Aggregate with different Group By -

i have table user, company, parentcompany , table goal. each company have parentcompany, , each user inside 1 company. goal have number of action, , type of goal, user execute goal, , time executed.

i want calculate number of action in date range each type of goal, each user, company, , parent_company. number of action each company equal sum of action user reside in company.

more or less after join query, able table below, column id id of company, parent_id id of companyparent, , num number of goal user inside of id company.

id          parent_id            num ----------- -------------------- ----------------------- 1           3                    1 2           1                    2 3           1                    1 4           2                    4 

now want make below:

id          parent_id            sum_id         sum_parent ----------- -------------------- -------------- ------------- 1           3                    1              1 2           1                    2              3 3           1                    1              3 4           2                    4              4 

how can make works? can 1 of value (sum_id or sum_parent) group by,

select id,sum(num) sum_id tablea group id 

or

select parent_id,sum(num) sum_parent tablea group parent_id 

but there way make in 1 query? tablea results query 5 join inside.

try this:

select a.id, a.parent_id, a.sum_id, b.sum_parent (select id, parent_id, sum(num) sum_id tablea group id)  inner join (select parent_id, sum(num) sum_parent tablea group parent_id) b on a.parent_id = b.parent_id