From: | Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org general" <pgsql-general(at)postgresql(dot)org> |
Subject: | remove some rows from resultset |
Date: | 2012-04-19 04:26:49 |
Message-ID: | CAM6mieL4b_GSXBuCbz3900Y5Un5d3Gv0FbdaU0JXB6vbANyGrg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I have the following table:
org_id | contract_name | org_specific_rule | count
----------+------------------+-------------------+-------
smpj28p2 | Group 123 | f | 3
smpj28p2 | Group 2 | f | 3
smpj28p2 | Group 2 | t | 9
smpj28p2 | Group 1 | f | 1
w37mtn4r | Group 123 | f | 26
w37mtn4r | Group 2 | f | 56
w37mtn4r | Group 1 | f | 55
Based on org_specific_rule and (org_id, contract_name) I need to transform this:
smpj28p2 | Group 2 | f | 3
smpj28p2 | Group 2 | t | 9
to
smpj28p2 | Group 2 | 9
in other words:
- if org_specific_rule = t then update "count" value in row where
org_specific_rule = f to value from this row (3 was updated to 9)
- remove org_specific_rule column
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;
org_id | contract_name | count
----------+------------------+----------
smpj28p2 | Group 123 | 3
smpj28p2 | Group 2 | 9
smpj28p2 | Group 1 | 1
w37mtn4r | Group 123 | 26
w37mtn4r | Group 2 | 56
w37mtn4r | Group 1 | 55
Any ideas?
--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Konoplev | 2012-04-19 05:27:23 | Re: Log to syslog in one line |
Previous Message | Michael Paquier | 2012-04-19 03:24:59 | Re: Compile docs on ArchLinux |