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

i've been struggling problem hours, though i'm sure there easy answer. i'm attempting gather monthly information , year-to-date information same table. i'm joining second table gather group name.

expense table:

+-----------------+---------------+------+-----+---------+----------------+ | field           | type          | null | key | default |          | +-----------------+---------------+------+-----+---------+----------------+ | 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 |          | +--------------+-------------+------+-----+---------+----------------+ | id           | int(5)      | no   | pri | null    | auto_increment | | account_code | varchar(14) | no   |     | null    |                | | group_name   | varchar(30) | no   |     | null    |                | +--------------+-------------+------+-----+---------+----------------+ 

i can come either monthly or year-to-date information, no matter i'm not able have both. below closest can come with, takes forever execute , results not whats expected:

select account_data.group_name, sum(m.debit)- sum(m.credit) month, sum(y.debit)- sum(y.credit) year account_data inner join expense m on m.account_data_id = account_data.id , month(m.`date`) in (7,8,9,10,11,12) left join expense y on y.account_data_id = account_data.id , month(y.`date`) in (7) group account_data.group_name 

this i'm looking accomplish:

+--------------+----------+---------+ | group_name   | month    | year    |  +--------------+----------+---------+ | payroll      | 10,000   | 50,000  | | payroll tax  | 1,000    | 5,000   | | benefits     | 500      | 1,000   |   +--------------+----------+---------+ 

any appreciated. i'm new here , hope i've followed rules , have provided of enough information help, if not let me know , provide more.

@philwinkle -your solution, 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),'' ) account_data ad left join expense_2011 e on e.account_data_id = ad.id e.account_data_id > 7 group ad.group_name 

the solution here use conditional sums... i'm going put pseudo-code , i'll edit when i'm 100% positive below solution validates , works:

select ad.group_name, (if(e.account_data_id in (7,8,9,10,11,12),sum(e.debit)- sum(e.credit)) month, (if(e.account_data_id=7,sum(e.debit)- sum(e.credit)) year account_data ad left join expense e on e.account_data_id = ad.id e.account_data_id > 7 group account_data.group_name