From: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
---|---|
To: | Roxanne Reid-Bennett <rox(at)tara-lu(dot)com> |
Cc: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: What is the alternate of FILTER below Postgresql 9.4 ? |
Date: | 2015-02-24 23:30:06 |
Message-ID: | CAD3a31VccFt0xtcOKv-G7WeFxzQj6emDp-qak2oT75bjL1-WVw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
>
>>
>> I tried, but it is not giving the output exactly like *FILTER*.
>>
>> app_development=# select CASE WHEN lower(location) = 'x' THEN COUNT(id)
>> ELSE 0 END AS X_loc, CASE WHEN lower(location) != 'x' THEN COUNT(id) ELSE 0
>> END AS Non_X_loc from people group by lower(location);
>> x_loc | non_x_loc
>> -------+-----------
>> 0 | 1
>> 2 | 0
>> (2 rows)
>> app_development=# select count(CASE WHEN lower(location) = 'x' THEN 1
>> END) AS X_loc, count(CASE WHEN lower(location) != 'x' THEN 1 END) AS
>> Non_X_loc from people group by lower(location);
>> x_loc | non_x_loc
>> -------+-----------
>> 0 | 1
>> 2 | 0
>> (2 rows)
>>
>> It is 2 rows output.
>>
>>
> then don't include the group by (which forces 1 row per location)
>
> select sum(case WHEN lower(location) = 'x' THEN 1 else 0 end) AS x_loc,
> sum(case WHEN lower(location) != 'x' THEN 1 else 0 end) AS not_x_loc
> from people;
>
>
> Roxanne
>
>
Also, if performance is not a big concenr, you can define a little
function, which I find makes the queries easier to read:
CREATE OR REPLACE FUNCTION or_null( boolean ) RETURNS boolean AS $$
SELECT CASE WHEN $1 THEN TRUE ELSE NULL END;
$$ LANGUAGE sql IMMUTABLE;
select count(or_null(lower(location) = 'x')) AS x_loc,
count(or_null(lower(location) != 'x'))) AS not_x_loc
from people;
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
From | Date | Subject | |
---|---|---|---|
Next Message | Dowwie | 2015-02-24 23:32:28 | Re: Row-level Security vs Application-level authz |
Previous Message | David Steele | 2015-02-24 20:48:50 | Re: Row-level Security vs Application-level authz |