MySql query same table to gather to-date and year-to-date information

I've been struggling with this problem for hours, even though I'm sure there is an easy answer. I'm attempting to gather monthly information and year-to-date information from the same table. I'm also joining a second table to gather the group name.

expense Table:

+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(5)        | NO   | PRI | NULL    | auto_increment |
| account         | char(14)      | NO   |     | NULL    |                |
| batch           | int(5)        | NO   |     | NULL    |                |
| date            | date          | NO   |     | NULL    |                |
| description     | varchar(50)   | NO   |     | NULL    |                |
| debit           | decimal(10,2) | NO   |     | NULL    |                |
| credit          | decimal(10,2) | NO   |     | NULL    |                |
| account_data_id | varchar(14)   | NO   |     | NULL    |                |
+-----------------+---------------+------+-----+---------+----------------+

account_data table:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(5)      | NO   | PRI | NULL    | auto_increment |
| account_code | varchar(14) | NO   |     | NULL    |                |
| group_name   | varchar(30) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

I can easily come up with either monthly or year-to-date information, but no matter what I do I'm not able to have both. Below is the closest I can come up with, but it takes forever to execute and the results are not whats expected:

SELECT account_data.group_name, sum(m.debit)- sum(m.credit) AS month, sum(y.debit)- SUM(y.credit) AS year
FROM account_data
INNER JOIN expense m ON m.account_data_id = account_data.id
AND MONTH(m.`date`) IN (7,8,9,10,11,12)
LEFT JOIN expense y ON y.account_data_id = account_data.id
AND MONTH(y.`date`) IN (7)
GROUP BY account_data.group_name

This is what I'm looking to accomplish:

+--------------+----------+---------+
| group_name   | month    | year    | 
+--------------+----------+---------+
| Payroll      | 10,000   | 50,000  |
| Payroll Tax  | 1,000    | 5,000   |
| Benefits     | 500      | 1,000   |  
+--------------+----------+---------+

Any help is greatly appreciated. I'm new here and I hope开发者_JS百科 I've followed all rules and have provided any of you with enough information to help, but if not let me know and I will provide more.

@philwinkle -Your solution, properly modified:

SELECT ad.group_name,
IF(MONTH(e.date) IN (7,8,9,10,11,12), SUM(e.debit) - SUM(e.credit),'' ),
IF(MONTH(e.date) = 7, SUM(e.debit) - SUM(e.credit),'' )
FROM account_data ad
LEFT JOIN expense_2011 e ON e.account_data_id = ad.id
WHERE e.account_data_id > 7
GROUP BY ad.group_name

---

The solution here is to use conditional sums... I'm going to put pseudo-code and I'll edit when I'm 100% positive the below solution validates and works:

SELECT ad.group_name,
(if(e.account_data_id IN (7,8,9,10,11,12),sum(e.debit)- sum(e.credit)) AS month,
(if(e.account_data_id=7,sum(e.debit)- sum(e.credit)) AS year
FROM account_data ad
LEFT JOIN expense e ON e.account_data_id = ad.id
WHERE e.account_data_id > 7
GROUP BY account_data.group_name