How i can write this query in PostgreSQL?

Question

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

Gordon Linoff · Accepted Answer · 2016-04-23 14:58:13Z

1

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

answered Apr 23, 2016 at 14:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How i can write this query in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related