Analyzing Color Breakdowns by Name

July 7, 2024

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: