UNION ALL statement needs to group data results 1

[dolodolo]

Hi,

I'd like to combine the results from this UNION ALL:

select pr.hmy,
count(pm.hmy)
from
pmuserex pm
inner join property pr on pr.hmy = pm.hproperty
inner join person p on p.hmy = pm.hobjpointer
inner join room r on r.hmyperson = pm.hobjpointer
where pr.hmy = 510
group by pr.hmy
UNION ALL
select pr.hmy,
count(pm.hmy)
from
pmuserex pm
inner join property pr on pr.hmy = pm.hproperty
inner join person p on p.hmy = pm.hobjpointer
inner join tenant t on t.hmyperson = pm.hobjpointer
and pr.hmy = 510
group by pr.hmy

Currently it returns:
510 3
510 263

I'd like it to return:

510 266


Any help would be most appreciated.
Thanks

 

[markros]

Use this UNION ALL as derived table, e.g.

select hmy, sum(SubTotal) as Total from (
select pr.hmy, 
count(pm.hmy) as SubTotal
 from 
pmuserex pm
inner join property pr on pr.hmy = pm.hproperty
inner join person p on p.hmy = pm.hobjpointer
inner join room r on r.hmyperson = pm.hobjpointer
where pr.hmy = 510
group by pr.hmy
UNION ALL
select  pr.hmy, 
count(pm.hmy)
 from 
pmuserex pm
inner join property pr on pr.hmy = pm.hproperty
inner join person p on p.hmy = pm.hobjpointer
inner join tenant t on t.hmyperson = pm.hobjpointer
and pr.hmy = 510
group by pr.hmy) X GROUP BY HMY

PluralSight Learning Library