CODEWITHSUNDEEP
mysqlsqldatabasepostgresql
Usm
Usm

Reputation: 451

How i can write this query in PostgreSQL?

Original from MySQL. There I want to know how many separate rows with columns by condition:

SELECT
sum(status='waiting'),
sum(source='twitter'),
sum(no_send_before <= '2009-05-28 03:17:50'),
sum(tries <= 20),
count(*)
FROM table_name


*************************** 1. row ***************************
                      sum(status ='waiting'): 550
                       sum(source='twitter'): 37271
sum(no_send_before <= '2009-05-28 03:17:50'): 36975
                            sum(tries <= 20): 36569
                                    count(*): 37271

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

For a query that is consistent between the two databases, use case:

SELECT sum(case when status='waiting' then 1 else 0 end),
       sum(case when source='twitter' then 1 else 0 end),
       sum(case when no_send_before <= '2009-05-28 03:17:50' then 1 else 0 end),
       sum(case when tries <= 20 then 1 else 0 end),
       count(*)
FROM table_name;

For a shorter, Postgres-specific syntax:

SELECT sum((status='waiting')::int),
       sum((source='twitter')::int)),
       sum((no_send_before <= '2009-05-28 03:17:50'))::int),
       sum((tries <= 20))::int),
       count(*)
FROM table_name

Upvotes: 1

Related Questions