CODEWITHSUNDEEP
mysqlgroup-bygroup-concat
Petah
Petah

Reputation: 46040

GROUP_CONCAT return NULL if any value is NULL

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

Upvotes: 6

Views: 6330

Answers (1)

Mark Byers
Mark Byers

Reputation: 837946

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

Upvotes: 5

Related Questions