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

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(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.
>

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