CODEWITHSUNDEEP
mysql
Uahmed
Uahmed

Reputation: 1845

Combine data of two tables

i have 2 tables

table1 and it has columns

 id 
 sdate
 type

type column can have 2 values events or schedule i am using this query to get the result of total events and scheudle

 select date_format(sdate, '%m-%d-%Y') as sdate, sum( type ='event') as tevent , sum(type='schedule') as tschedule from table1 where sid ='1' group by (sdate);

table 2 has these columns

 id
 title
 dtime

to make them both similar to use in union i did something like this

 select date_format(dtime, '%m-%d-%Y') as sdate ,0 as tevent,0 as tschedule,count(id) as tlog from table2 where sid =1  group by (sdate) ;

I am bit confuse that how can i get data from both table in a way that if the date are same it should show me data in one column .

Upvotes: 0

Views: 51

Answers (1)

Ashalynd
Ashalynd

Reputation: 12563

Try this (includes some correction of the original queries):

select sdate, sum(tevent) as tevent, 
sum(tschedule) as tschedule, sum(tlog) as tlog
from (
select date_format(sdate, '%m-%d-%Y') as sdate, 
sum(type='event') as tevent , 
sum(type='schedule') as tschedule,
0 as tlog from table1
group by sdate
union
select date_format(dtime, '%m-%d-%Y') as sdate ,
0 as tevent,0 as tschedule,
count(id) as tlog from table2 
group by sdate 
) s group by sdate;

Upvotes: 1

Related Questions