Counting two different fields from two different tables in one query

Question

I need to somehow put these two queries into one query:

1

select fac_name, datename(month,b.startdt) as 'month', year(b.startdt) as 'year', count(a.empfk) as '#filled'  
from tbl_tmx_attempt a  
left outer join tbl_tmx_activity b on a.activityfk = b.activity_pk  
left outer join tbl_tmx_actloc c on b.activity_pk = c.activityfk  
left outer join loc d on c.locfk = d.loc_pk  
left outer join fac e on d.loc_facfk = e.fac_pk  
where b.startdt > '12/31/08'    
group by fac_name, year(b.startdt), month(b.startdt), datename(month,b.startdt) 

2

select fac_name, datename(month,b.startdt) as 'month', year(b.startdt) as 'year', sum(b.maxcapacity) as 'capacity'  
from tbl_tmx_activity b   
left outer join tbl_tmx_actloc c on b.activity_pk = c.activityfk  
left outer join loc d on c.locfk = d.loc_pk  
left outer join fac e on d.loc_facfk = e.fac_pk  
where b.startdt > '12/31/08'  
group by fac_name, year(b.startdt), month(b.startdt), datename(month,b.startdt)  

Everything is basically the same except the counts are from different tables counting a different field. I need the results to be one table showing the capacity and #filled

Answer 1

Can't you just use a SQL Union operator? SQL Union Operator

Answer 2

You could use a Union statement and just add a null column in for each. Like this:

select fac_name, datename(month,b.startdt) as 'month', year(b.startdt) as 'year', count(a.empfk) as '#filled' , '' as 'capacity' 
from tbl_tmx_attempt a  
left outer join tbl_tmx_activity b on a.activityfk = b.activity_pk  
left outer join tbl_tmx_actloc c on b.activity_pk = c.activityfk  
left outer join loc d on c.locfk = d.loc_pk  
left outer join fac e on d.loc_facfk = e.fac_pk  
where b.startdt > '12/31/08'    
group by fac_name, year(b.startdt), month(b.startdt), datename(month,b.startdt)

Union All

select fac_name, datename(month,b.startdt) as 'month', year(b.startdt) as 'year', '' as '#filled', sum(b.maxcapacity) as 'capacity'  
from tbl_tmx_activity b   
left outer join tbl_tmx_actloc c on b.activity_pk = c.activityfk  
left outer join loc d on c.locfk = d.loc_pk  
left outer join fac e on d.loc_facfk = e.fac_pk  
where b.startdt > '12/31/08'  
group by fac_name, year(b.startdt), month(b.startdt), datename(month,b.startdt) 

If this fails to work because you can't do a group by then try inserting the rows into a temp table with both capacity and #filled columns. You would just leave one empty for each insert. If what you are looking for is 1 row with both results then I would do the same as above except insert the results into 2 separate temp tables and do an OUTER JOIN on their unique Columns which I think would be fac_name, month, and year. Comment on my post if that doesn't work or isn't what you are trying to do.

Answer 3

it would be easier w/ out the left joins.

as is, something like this would do:

select fac_name, [month], [year], max([#filled]) as [#filled], max(capacity) as [capacity]

from (

  select fac_name, datename(month,b.startdt) as 'month', year(b.startdt) as 'year', count(a.empfk) as '#filled', null as 'capacity'
  from tbl_tmx_attempt a  
  left outer join tbl_tmx_activity b on a.activityfk = b.activity_pk  
  left outer join tbl_tmx_actloc c on b.activity_pk = c.activityfk  
  left outer join loc d on c.locfk = d.loc_pk  
  left outer join fac e on d.loc_facfk = e.fac_pk  
  where b.startdt > '12/31/08'    
  group by fac_name, year(b.startdt), month(b.startdt), datename(month,b.startdt) 

  union all

  select fac_name, datename(month,b.startdt) as 'month', year(b.startdt) as 'year', null as '#filled', sum(b.maxcapacity) as 'capacity'  
  from tbl_tmx_activity b   
  left outer join tbl_tmx_actloc c on b.activity_pk = c.activityfk  
  left outer join loc d on c.locfk = d.loc_pk  
  left outer join fac e on d.loc_facfk = e.fac_pk  
  where b.startdt > '12/31/08'  
  group by fac_name, year(b.startdt), month(b.startdt), datename(month,b.startdt)  

  ) a

group by fac_name, [month], [year]