sqldb2

Analyzing Color Breakdowns by Name


I have the following table:

CREATE TABLE Colors (
    name INT,
    color CHAR(1)
);

INSERT INTO Colors (name, color) VALUES
(1, 'r'),
(1, 'r'),
(1, 'b'),
(2, 'b'),
(2, 'b'),
(2, 'r'),
(3, 'b'),
(3, 'b'),
(4, 'r');


     name color
        1     r
        1     r
        1     b
        2     b
        2     b
        2     r
        3     b
        3     b
        4     r

I want to add a column to this table that says which colors each name belongs to:

 name color     new
    1     r r and b
    1     r r and b
    1     b r and b
    2     b r and b
    2     b r and b
    2     r r and b
    3     b  only b
    3     b  only b
    4     r  only r

Normally, I would use the distinct statement to do this:

SELECT 
    C.name,
    C.color,
    CASE 
        WHEN COUNT(DISTINCT C1.color) > 1 THEN 'r and b'
        WHEN MAX(C1.color) = 'r' THEN 'only r'
        ELSE 'only b'
    END as new
FROM 
    myt C
JOIN 
    myt C1 ON C.name = C1.name
GROUP BY 
    C.name, 
    C.color;

But I am trying to re-write this code so that it explicitly looks at if a name has red or blue ... not just using distinct color.

Can someone please show me how to re-write this SQL code?

Thanks!


Solution

  • Try utilizing CONCAT:

    WITH ColorSummary AS (
        SELECT
            name,
            CASE
                WHEN COUNT(DISTINCT color) = 1 THEN CONCAT('only ', MIN(color))
                ELSE 'r and b'
            END AS color_summary
        FROM Colors
        GROUP BY name
    )
    SELECT
        c.name,
        c.color,
        cs.color_summary AS new
    FROM
        Colors c
    JOIN
        ColorSummary cs
    ON
        c.name = cs.name;
    

    Output:

    name color new
    1 r r and b
    1 r r and b
    1 b r and b
    2 b r and b
    2 b r and b
    2 r r and b
    3 b only b
    3 b only b
    4 r only r