sql - How to have Counts in a Join query -

i have create join between 2 tables on column , display counts of fields on joined

for example here 'business' key on want join.

the first query

select      [business], count(*) total dimhexpand group [business]  

and result as:

da  54100  dual    6909  ecm 1508  flex    15481 

another query :

select business, count (*) lodg group business order business 

the result of query :

da  100  dual    909  ecm 508  flex    15481 

i want return data joining these 2 tables show like

**dimhexpand.business dimhexpand.count lodg.count** da          54100       100   dual            6909        909  ecm         1508        508  flex            15481       151481 

you can join 2 tables on business column:

select d.business,    count(d.business) dimcount,   l.lodgcount dimhexpand d left join (   select business, count (*) lodgcount   lodg    group business  ) l   on d.business = l.business group d.business; 

if might have different business values in each table, can use full outer join between both queries, similar this:

select coalesce(d.business, l.business),   coalesce(d.dimcount, 0) dimcount,   coalesce(l.lodgcount, 0) lodgcount (   select business, count(*) dimcount    dimhexpand    group business ) d full outer join (   select business, count (*) lodgcount   lodg    group business  ) l   on d.business = l.business