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@xxxxxxxxxxxxxx>:
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
