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?