How to select the sum from three tables and group by date I have 3 tables.  works,software,agent

it shows like that:



agent:

id  name
1   agent1
2   agent2
3   agent3
4   agent4


works

id  name    date(TIMESTAMP type)    agent_id
1   w1  2014/1/1    1
2   w2  2014/2/2    1
3   w3  2014/1/3    2
4   w4  2014/1/4    2
5   w5  2014/1/5    3
6   w6  2014/1/6    4
7   w7  2014/1/7    4
8   w8  2014/1/8    4
9   w9  2014/3/9    4


software

id  name    date(TIMESTAMP type)  agent_id
1   s1  2014/1/1    1
2   s2  2014/2/2    1
3   s3  2014/1/3    2
4   s4  2014/1/4    2
5   s5  2014/1/5    3
6   s6  2014/1/6    4
7   s7  2014/1/7    4
8   s8  2014/1/8    4
9   s9  2014/3/9    4




and I want to get the result like that: get the every month of current year sum of works + software connect by agent_id

Jan-14  Feb-14  Mar-14  Apr-14  May-14  Jun-14  Jul-14  Aug-14  Sep-14  Oct-14  Nov-14  Dec-14
agent1  2   2   0   0   0   0   0   0   0   0   0   0
agent2  4   0   0   0   0   0   0   0   0   0   0   0
agent3  2   0   0   0   0   0   0   0   0   0   0   0
agent4  6   0   2   0   0   0   0   0   0   0   0   0


How to write the sql in MySql?
With other servers you  can use common table expressions.  With MySQL you can use derived tables as in:

select name, year, month, sum(cnt) from
(select agent.name, year(date) as year, monthname(date) as month, count(*) as cnt
from
agent inner join software on (agent.id = software.agent_id) group by agent.name, year(date), monthname(date)
union (select agent.name, year(date) as year, monthname(date) as month, count(*) as cnt
from 
agent inner join works on (agent.id = works.agent_id)
group by agent.name, year(date), monthname(date))) T1
group by name, year, month