I have a query that returns me the following data.
1 - Category 1
1.1 Sub Category 1
1.1.1 - Sub Sub Category 1 $10
1.1.2 - Sub Sub Category 2 $10
1.1.3 - Sub Sub Category 3 $10
1.2- Sub Category 1
1.2.1 - Sub Sub Category 1 $20
1.2.2 - Sub Sub Category 2 $20
1.2.3 - Sub Sub Category 3 $20
2 - Category 2
2.1 - Sub Category 2
2.1.1 - Sub Sub Category 1 $30
2.1.2 - Sub Sub Category 2 $30
2.1.3 - Sub Sub Category 3 $30
I wanted my query returned under each sub category total. Wanted it to be as shown below ex:
1 - Category 1
1.1 Sub Category 1
1.1.1 - Sub Sub Category 1 $10
1.1.2 - Sub Sub Category 2 $10
1.1.3 - Sub Sub Category 3 $10
total $30
1.2- Sub Category 1
1.2.1 - Sub Sub Category 1 $20
1.2.2 - Sub Sub Category 2 $20
1.2.3 - Sub Sub Category 3 $20
total $60
2 - Category 2
2.1 - Sub Category 2
2.1.1 - Sub Sub Category 1 $30
2.1.2 - Sub Sub Category 2 $30
2.1.3 - Sub Sub Category 3 $30
total $90
I use PHP with MySql
My query is this way
SELECT cm.cat, cm.subcat, cm.subsubcat, cm.title, cm.typo,
(SELECT SUM(rg.value) FROM tt_record rg WHERE rg.cat = cm.cat AND
rg.subcat = cm.subcat AND rg.subsubcat = cm.subsubcat
AND rg.igreja = 'XXX' AND MONTH(rg.date) = 01 AND YEAR(rg.date) = 2015) Sum,
(SELECT SUM(rg.value) FROM tt_record rg WHERE rg.cat = cm.cat
AND rg.subcat = cm.subcat AND cm.subsubcat IS NULL
AND rg.igreja = 'XXX' AND MONTH(rg.date) = 01 AND YEAR(rg.date) = 2015) SubCategory,
(SELECT SUM(rg.value) FROM tt_record rg WHERE rg.cat = cm.cat AND cm.subcat IS NULL
AND cm.subsubcat IS NULL AND rg.igreja = 'XXX' AND MONTH(rg.date) = 01 AND YEAR(rg.date) = 2015
) Category
FROM tt_cta_mestre cm
LEFT JOIN tt_cta_mestre cm2 ON cm2.cat = cm.cat AND cm2.subcat = cm.subcat
AND cm2.subsubcat = cm.subsubcat
ORDER BY cm.cat, cm.subcat, cm.subsubcat
tt_cta_mestre the table is where I store my categories, sub categories and sub sub categories. They have the cat field, subcat, subsubcat. If the Category subcat fields and subsubcat will be empty.
If the subcategory subsubcat field'll be empty.
If subsubcat the three fields are populated.
The table where I store record is the inputs and outputs with their values. Each record is linked to a subsubcat