CODEWITHSUNDEEP
mysql
haz
haz

Reputation: 780

mysql conditional select use in a sum

I have a joined table that includes the following columns:

id_cat | category | risk_factor | rs | id_sample | R1 | R2 | rs

and I want to generate a table like:

category | total | high | medium | low

where total, high, medium and low are sums that are derived from a calculation that determines if the 'risk_factor' is equal to 'R1' and or 'R2' for that row,

as per the following pseudocode

SELECT
        category, 
        count(*) as total,
        sum(case when R1 = risk_factor and R2 = risk_factor) = 1 as high,
        sum(case when R1 = risk_factor or R2 = risk_factor) = 1 as med,
        sum(case when R1 <> risk_factor and R2 <> risk_factor) = 1 as low
        FROM mydatabase.mytable1 
         JOIN mydatabase.mytable2 as r
            ON mytable1.rs=r.rs
                WHERE id_sample = 'sample1' 
                GROUP BY category
                ORDER BY category ASC;

My question is how to properly syntax the case statements within the sum statements.

Upvotes: 2

Views: 39

Answers (1)

MohanaPriyan
MohanaPriyan

Reputation: 218

SELECT category,

    COUNT(*) AS total,

    SUM(CASE WHEN R1 = risk_factor AND R2 = risk_factor THEN 1 ELSE 0 END ) AS high,

    SUM(CASE WHEN R1 = risk_factor OR R2 = risk_factor THEN 1 ELSE 0 END ) AS med,

    SUM(CASE WHEN R1 <> risk_factor AND R2 <> risk_factor THEN 1 ELSE 0 END ) AS low

    FROM mydatabase.mytable1 
     JOIN mydatabase.mytable2 AS r
        ON mytable1.rs=r.rs
            WHERE id_sample = 'sample1' 
            GROUP BY category
            ORDER BY category ASC;

Upvotes: 2

Related Questions