then don't include the group by (which forces 1 row per location)
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.
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.
