Обсуждение: What is the alternate of FILTER below Postgresql 9.4 ?

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 ?

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

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°

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

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

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

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
themthe science. 
Donald Knuth