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