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

On Tuesday, February 24, 2015 06:13:21 PM Pavel Stehule wrote:
> Hi
>
> 2015-02-24 17:02 GMT+01:00 Arup Rakshit <aruprakshit(at)rocketmail(dot)com>:
>
> > Hi,
> >
> > Please look at my query :
> >
> > [shreyas(at)rails_app_test (master)]$ rails db
> > psql (9.4.1)
> > Type "help" for help.
> >
> > app_development=# select id, location, name from people;
> > id | location | name
> > ----+----------+------
> > 2 | X | foo
> > 3 | X | foo
> > 4 | Y | foo
> > (3 rows)
> >
> > app_development=# SELECT COUNT(id) FILTER(WHERE lower(location) != 'x') AS
> > Non_X_loc, COUNT(id) FILTER (WHERE lower(location) = 'x') AS X_loc FROM
> > "people";
> > non_x_loc | x_loc
> > -----------+-------
> > 1 | 2
> > (1 row)
> >
> > This *FILTER* method is available from 9.4, How can I get the same output
> > below 9.4 version ?
> >
> >
> use SQL CASE
>
> SELECT COUNT(CASE lower(location) <> 'x' THEN 1 END), ...
>
> attention: "lower" function is slow - so don't use it if it is not necessary
>
> Regards
>
> Pavel Stehule

Pavel,

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.

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

--Brian Kernighan