MYSQL MULTIPLE COUNT AND SUM

MYSQL MULTIPLE COUNT AND SUM

I hope this is possible in MYSQL, I am scripting with PHP.
I am trying to create multiple column on SUM of values and COUNT on table1
based on each month based with individual conditions and groupings. The
tables are already joined through the accountid. I have two tables
monthlyreport(table1) & planters(table2).
Desired Results is in table 1
MONTHLY REPORT (Table 1)
REPORTID|ACCOUNTID|COMPMONTH|SUMtoDATE|COUNTtoDATE|SUMcompDATE|COUNTcompDATE|
1 | 190 | JAN | 150 | 2 | 150 |
2 |
2 | 190 | FEB | 0 | 0 | 100 |
1 |
Planters (Table 2)
PlanterID | ACCOUNTID |PLANTER | SALARY | compDATE | toDATE |
1 | 190 | aaa | 100 | Jan-1-2013 | Jan-05-2013 |
2 | 190 | bbb | 50 | Jan-9-2013 | Jan-12-2013 |
3 | 190 | aaa | 100 | Feb-1-2013 | Mar-12-2013 |
4 | 190 | bbb | 0 | Mar-5-2013 | Mar-12-2013 |
A single query with inner join already works but if I run both I get
nothing because I can't seem to get the logic if it is possible.
This is what I have so far from stackoverflow but getting error. Wish
someone can refactor it or make it work.
SELECT *,
(
SELECT COUNT(planters.todate), SUM(planters.todate)
FROM monthlyreport
INNER JOIN planters ON monthlyreport.accountid = planters.accountid
WHERE monthlyreport.accountid = 190 AND MONTH(monthlyreport.compmonth) =
MONTH(planters.todate)
GROUP BY monthlyreport.mthreportid, month(planters.todate)
) AS count_1,
(
SELECT COUNT(planters.compdate), SUM(planters.compdate)
FROM monthlyreport
INNER JOIN planters ON monthlyreport.accountid = planters.accountid
WHERE monthlyreport.accountid = 190 AND MONTH(monthlyreport.compmonth) =
MONTH(planters.compdate)
GROUP BY monthlyreport.mthreportid, month(planters.compdate)
) AS count_2