2

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?

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.