sql - Joining two SELECT Statements with WHERE clause -

i think easy im not practiced in sql , dont know of syntax.

basically got big table different user complaints (represented problem id) , timestamps want graph.

the individual statements easy , straightforward. example:

select date( datetimebegin ) date, count( * ) cntprob1 `problems`  problemid = "1" group date, problemid;  select date( datetimebegin ) date, count( * ) cntprob2 `problems`  problemid = "2" group date, problemid; 

each table gives me pretty simple output:

date, cntprob1 2013-03-11,4 2013-03-14,1 2013-03-17,7  date, cntprob2 2013-03-12,2 2013-03-13,1 2013-03-14,3 2013-03-17,1 

i need result combined this:

date, cntprob1, cntprob2 2013-03-11,4,0 2013-03-12,0,2 2013-03-13,0,1 2013-03-14,1,3 2013-03-17,7,1 

i guess simple if know right sql syntax... kind of join?!

any appreciated!

you don't need join result, should able using case expression inside of aggregate function:

select      date(datetimebegin) date,      sum(case when problemid = '1' 1 else 0 end) cntprob1,     sum(case when problemid = '2' 1 else 0 end) cntprob2 `problems`  problemid in ('1', '2') group date(datetimebegin); 

if want use count() instead of sum() use:

select      date(datetimebegin) date,      count(case when problemid = '1' problemid end) cntprob1,     count(case when problemid = '2' problemid end) cntprob2 `problems`  problemid in ('1', '2') group date(datetimebegin); 

see sql fiddle demo of both queries.