Search Postgresql Archives

Re: What is the alternate of FILTER below Postgresql 9.4 ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux