How can I make GROUPT_CONCAT
return NULL
if any column is NULL
?
Here is a test table:
CREATE TABLE gc (
a INT(11) NOT NULL,
b VARCHAR(1) DEFAULT NULL
);
INSERT INTO gc (a, b) VALUES
(1, 'a'),
(1, 'b'),
(2, 'c'),
(2, NULL),
(3, 'e');
And my query:
SELECT a, GROUP_CONCAT(b)
FROM gc
GROUP BY a;
This is what I get:
a | GROUP_CONCAT(b)
--+----------------
1 | a,b
2 | c
3 | e
This is what I want:
a | GROUP_CONCAT(b)
--+----------------
1 | a,b
2 | NULL
3 | e
In an IF
expression check if any value is NULL in the group. I can think of a couple of ways of doing that:
1) Count the non-null values and compare it to the number of rows in the group:
SELECT a, IF(COUNT(b) = COUNT(*), GROUP_CONCAT(b), NULL) AS bs
FROM gc
GROUP BY a
See it working online: sqlfiddle
2) Count the number of null values using SUM
:
SELECT a, IF(SUM(b IS NULL) = 0, GROUP_CONCAT(b), NULL) AS bs
FROM gc
GROUP BY a
See it working online: sqlfiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With