1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] Conditional count of rows where at least one peer qualifies

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 27, 2021.

  1. Stack

    Stack Membro Participativo

    Background

    I'm a novice SQL user. Using PostgreSQL 13 on Windows 10 locally, I have a table t:

    +--+---------+-------+
    |id|treatment|outcome|
    +--+---------+-------+
    |a |1 |0 |
    |a |1 |1 |
    |b |0 |1 |
    |c |1 |0 |
    |c |0 |1 |
    |c |1 |1 |
    +--+---------+-------+


    The Problem

    I didn't explain myself well initially, so I've rewritten the goal.

    Desired result:

    +-----------------------+-----+
    |ever treated |count|
    +-----------------------+-----+
    |0 |1 |
    |1 |3 |
    +-----------------------+-----+


    First, identify id that have ever been treated. Being "ever treated" means having any row with treatment = 1.

    Second, count rows with outcome = 1 for each of those two groups. From my original table, the ids who are "ever treated" have a total of 3 outcome = 1, and the "never treated", so to speak, have 1 `outcome = 1.

    What I've tried

    I can get much of the way there, I think, with something like this:

    select treatment, count(outcome)
    from t
    group by treatment;


    But that only gets me this result:

    +---------+-----+
    |treatment|count|
    +---------+-----+
    |0 |2 |
    |1 |4 |
    +---------+-----+

    Continue reading...

Compartilhe esta Página