Data Mashing

[SaAether]

Hey peoples,

Im having an issue mashing data from multiple tables together. Me as well as some co-workers have been playing with it but alas no luck.
Im sure its some newbie mistake but here it goes...

Query 1

select orgid, sum(calls) as 'Calls'
from dbo.TechL_LOps_CHandled_View
where dayid = 20080120
group by orgid
order by orgid

Output

orgid Calls
======================================
64 8
86 20
129 11
140 6
149 12
etc etc


Query 2

select orgid, sum(login_time) as 'Login Time'
from dbo.TechL_LOps_WorkLog_View
where dayid = 20080120
group by orgid
order by orgid

Output

orgid Login Time
======================================
31 49.62
64 427.05
86 523.00
129 320.85
140 125.25
etc etc


Now i want to put these together IE the following

orgid Calls Login Time
======================================
31 0 49.62
64 8 427.05
86 20 523.00
129 11 320.85
140 6 125.25
etc etc

So i figured something like

select t1.orgid, sum(t1.calls) as 'Calls', sum(t2.login_time) as 'Login Time'
from dbo.TechL_LOps_CHandled_View t1 join dbo.TechL_LOps_WorkLog_View t2
on t1.orgid = t2.orgid
where t1.dayid = 20080120
and t2.dayid = t1.dayid
group by t1.orgid
order by t1.orgid

would work but quite the opposite

Output

orgid Calls Login Time
======================================
64 96 427.05
86 80 523.00
129 33 641.70
140 6 125.25
149 72 706.74
etc etc

Not only are the calls being mulitplied for some reason but the orgid 31 record isnt showing up as well.

Any help would be really appreciated.

Thanks

[ck9663]

Hey peoples,

Im having an issue mashing data from multiple tables together. Me as well as some co-workers have been playing with it but alas no luck.
Im sure its some newbie mistake but here it goes...

Query 1

select orgid, sum(calls) as 'Calls'
from dbo.TechL_LOps_CHandled_View
where dayid = 20080120
group by orgid
order by orgid

Output

orgid Calls
======================================
64 8
86 20
129 11
140 6
149 12
etc etc


Query 2

select orgid, sum(login_time) as 'Login Time'
from dbo.TechL_LOps_WorkLog_View
where dayid = 20080120
group by orgid
order by orgid

Output

orgid Login Time
======================================
31 49.62
64 427.05
86 523.00
129 320.85
140 125.25
etc etc


Now i want to put these together IE the following

orgid Calls Login Time
======================================
31 0 49.62
64 8 427.05
86 20 523.00
129 11 320.85
140 6 125.25
etc etc

So i figured something like

select t1.orgid, sum(t1.calls) as 'Calls', sum(t2.login_time) as 'Login Time'
from dbo.TechL_LOps_CHandled_View t1 join dbo.TechL_LOps_WorkLog_View t2
on t1.orgid = t2.orgid
where t1.dayid = 20080120
and t2.dayid = t1.dayid
group by t1.orgid
order by t1.orgid

would work but quite the opposite

Output

orgid Calls Login Time
======================================
64 96 427.05
86 80 523.00
129 33 641.70
140 6 125.25
149 72 706.74
etc etc

Not only are the calls being mulitplied for some reason but the orgid 31 record isnt showing up as well.

Any help would be really appreciated.

Thanks

try something like:

select orgid, sum(calls) as totalcalls, sum(logintime) as totallogintime from
(select orgid, calls, 0 as logintime from dbo.TechL_LOps_CHandled_View
union
select orgid, 0 as calls, logintime from dbo.TechL_LOps_WorkLog_View) A
group by origid

-- ck

[SaAether]

Thank you very much, I should have posted ealier instead of wasting so much time my self heh