I have this table

person  outcome
Peter   positive
Peter   positive
Peter   positive
Eric    positive
Eric    positive
Eric    negative

and want to count the number of rows each person has a positive/negative outcome.

select person, outcome, count(*)
from public.test123
group by person, outcome

person  outcome count
Peter   positive    3
Eric    positive    2
Eric    negative    1

But I also want a zero count for Peter negative. I've seen answers like this but I have nothing to join the table to?

How can I groupby, count and include zeros?

person  outcome count
Peter   positive    3
Peter   negative    0
Eric    positive    2
Eric    negative    1

zxc

create table public.test123 (
    person      VARCHAR(20),
    outcome     VARCHAR(20));
    
insert into public.test123(person, outcome)
    VALUES
        ('Peter', 'positive'),
        ('Peter', 'positive'),
        ('Peter', 'positive'),
        ('Eric', 'positive'),
        ('Eric', 'positive'),
        ('Eric', 'negative');

CodePudding user response：

step-by-step demo:db<>fiddle

SELECT
    s.person,
    s.outcome,
    SUM((t.outcome IS NOT NULL)::int) as cnt                           -- 4
FROM (
    SELECT
        *
    FROM unnest(ARRAY['positive', 'negative']) as x(outcome),          -- 1
    (
        SELECT DISTINCT                                                -- 2
            person
        FROM test123
    ) s
) s
LEFT JOIN test123 t ON t.person = s.person AND t.outcome = s.outcome   -- 3
GROUP BY s.person, s.outcome

1. Create a list of all possible outcome values.
2. Join it with all possible person values. Now you have a cartesian table with all possible combinations.
3. This can be used to join your original table.
4. Count all non-NULL values for each combination (in that case I uses SUM() with all non-NULL values == 1, 0 else)