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 sum them.

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.