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@rocketmail.com>:

Hi,

Please look at my query :

[shreyas@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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tuesday, February 24, 2015 06:14:53 PM Andreas Kretschmer wrote:

Arup Rakshit <aruprakshit@rocketmail.com> wrote:

This *FILTER* method is available from 9.4, How can I get the same output below 9.4 version ?

case when ...

see:
http://www.cybertec.at/postgresql-9-4-aggregation-filters-they-do-pay-off/

Andreas

Humm. It is the one I wanted. But I used it, with group by, which is why I got 2 rows output.

Thanks!!

--
================
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tuesday, February 24, 2015 06:14:53 PM Andreas Kretschmer wrote:

Arup Rakshit <aruprakshit@rocketmail.com> wrote:

This *FILTER* method is available from 9.4, How can I get the same output below 9.4 version ?

case when ...

see:
http://www.cybertec.at/postgresql-9-4-aggregation-filters-they-do-pay-off/

Andreas

Humm.. That's correct. I used the same with group by, that's why got 2 rows output.

Anway thanks for your help! :-)

--
================
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi

2015-02-24 17:02 GMT+01:00 Arup Rakshit <aruprakshit@rocketmail.com>:

Hi,

Please look at my query :

[shreyas@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

Arup Rakshit <aruprakshit@rocketmail.com> wrote:

This *FILTER* method is available from 9.4, How can I get the same output below 9.4 version ?

case when ...

see:
http://www.cybertec.at/postgresql-9-4-aggregation-filters-they-do-pay-off/

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 2/24/2015 11:29 AM, Arup Rakshit wrote:

On Tuesday, February 24, 2015 06:13:21 PM Pavel Stehule wrote:

Hi

2015-02-24 17:02 GMT+01:00 Arup Rakshit <aruprakshit@rocketmail.com>:

Hi,

Please look at my query :

[shreyas@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.

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

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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/&gt;*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.