Re: remove some rows from resultset

On 19 Apr 2012, at 6:26, Ondrej Ivanič wrote:

> I have query which does everything but I have mixed feelings about it:
> select
>    b1.org_id, b1.contract_name, coalesce(b2.count, b1.count)  as count
> from (select * from billing where org_specific_rule = false) as b1
> left join billing b2 on
>    b1.org_id = b2.org_id
>    and b1.contract_name = b2.contract_name
>    and b2.org_specific_rule = true
> order by 1,2;


You don't need the subquery. Also, if you can have multiple 'false' rows for the same unique identifier, you'll want to
sumthem. 

Untested, but I think this is what you want:

select
   b1.org_id, b1.contract_name, sum(b1.count) + sum(coalesce(b2.count, 0))  as count
from billing as b1
left join billing b2 on
   b1.org_id = b2.org_id
   and b1.contract_name = b2.contract_name
   and b2.org_specific_rule = false
   and b1.org_specific_rule = true
group by b1.org_id, b1.contract_name
order by b1.org_id, b1.contract_name;


Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.