PostreSQL returning only specific value

I’ve got below structure:

sample_table

  code         | delivery       | end_date     | type
---------------+----------------+--------------+------
C086000-T10001 | OK             | 2014-11-12   | 01
C086000-T10001 | OK             | 2014-11-11   | 03
C086000-T10002 | FALSE          | 2014-12-10   | 03
C086000-T10002 | FALSE          | 2014-01-04   | 03
C086000-T10003 | FALSE          | 2014-02-28   | 03
C086000-T10003 | FALSE          | 2014-11-12   | 01
C086000-T10003 | FALSE          | 2014-08-20   | 01

I want to output how many code (counted) has the OK delivery status. I was trying to do something like:

SELECT sample_table.code AS code, sample_table.delivery AS delivered
FROM sample_table
WHERE COUNT(sample_table.delivery = "OK")
GROUP BY code, delivered

Edit The expected output should be like below

  code         | delivered      | all_type     |
---------------+----------------+--------------+
C086000-T10001 | 2              | 04           |
C086000-T10002 | 0              | 06           |
C086000-T10003 | 0              | 05           |

Answer

In Postgres, the filter() clause comes handy for this:

select 
    code,
    count(*) filter(where delivery = 'OK') delivered,
    sum(type) all_type
from sample_table
group by code

Advertisement

Answer