Query to count two fields-CodePudding

I am looking to do 2 counts on the data of a table with a grouping on a field. Here is an example of data :

 sends_id | status | result
---------- -------- ----------
 119      | er     | 7dccs11x
 119      | er     |
 120      | po     | 5bde48u1
 120      | pu     |
 121      | po     | a1d9tbg8

I try to do this :

Count the number of lines with sends_id identical -> n_total
Count the number of lines including status = "po" and result = non-empty value

What should give me this:

 sends_id | n_total | n_ok
-------------------- ------
 119      | 2       | 0
 120      | 2       | 1
 121      | 1       | 1

I wrote this :

WITH a AS (
    SELECT
        sends_id,
        COUNT(sends_id) AS n_total,
        CASE WHEN status = 'po' THEN 1 ELSE 0 END AS po
    FROM resource
    GROUP BY sends_id, status
)
SELECT sends_id, sum(po) AS n_ok, n_total
FROM a
GROUP BY sends_id, n_total;

 sends_id | n_total | n_ok
-------------------- ------
 119      | 2       | 0
 120      | 1       | 1
 121      | 1       | 1

What does not go is that the lines with 121 on 2 different statuses. I do not see how to take into account this ...

An idea ?

(sorry for my bad english...)

CodePudding user response：

What you want is called conditional aggregation.

select
  sends_id,
  count(*) as total,
  count(*) filter (where status = 'po') as po
from resource
group by sends_id
order by sends_id;