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!
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 |