6

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
Petah
  • 45,477
  • 28
  • 157
  • 213

1 Answers1

5

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

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Cool thanks :) I have a feeling this will perform terribly on a large data set though. – Petah Aug 23 '12 at 23:13
  • Just saw your update, do you know which one would perform better? – Petah Aug 23 '12 at 23:14
  • @Petah: I'd have to measure it to be sure. At a guess the second might be a bit faster, but I suspect that both will have reasonable performance. The expensive part is likely to be the `GROUP_CONCAT`. – Mark Byers Aug 23 '12 at 23:15