sql server 2008 - Sql Union a number of tables together -

i'm trying total count 4 different tables using union i'm not sure how work..

my code single table count works fine,

select (select count(*)          (select rn, dp, dt, min(ed) mindate                ecount1                group rn, dp, dt) x          dt = 'str'             , month(mindate) >= '07'            , month(mindate) <= '09') straft 

i thought add union doesn't work? this?

select (select count(*)  (select rn, dp, dt, min(ed) mindate ecount1 group rn, dp, dt) x dt = 'str' , month(mindate) >= '07' , month(mindate) <= '09' union select rn, dp, dt, min(ed) mindate ecount2 group rn, dp, dt) x dt = 'str' , month(mindate) >= '07' , month(mindate) <= '09' union select rn, dp, dt, min(ed) mindate ecount3 group rn, dp, dt) x dt = 'str' , month(mindate) >= '07' , month(mindate) <= '09' union select rn, dp, dt, min(ed) mindate ecount4 group rn, dp, dt) x dt = 'str' , month(mindate) >= '07' , month(mindate) <= '09' ) straft 

as suggested prissioner use union all

with cte (     select * ecount1      union     select * ecount2     union     select * ecount3      union     select * ecount4 ), calculate (     select rn, dp, dt, min(ed) mindate      cte      dt = 'str'         , month(ed) >= '07'        , month(ed) <= '09     group rn, dp, dt     having min(ed) not null ) select count(*)  calculate 

for count each month use group by

 select month(ed), count(*)  calculate  group month(ed) 

or use conditional count:

 select count(*),          count (case when month(ed) = '07' 1 end) count_07,         count (case when month(ed) = '08' 1 end) count_08,         count (case when month(ed) = '09' 1 end) count_09  calculate  group month(ed)