Hail to GROUP_CONCAT!

MySQL’s GROUP_CONCAT command is a phantastic thing, it can simplify big efforts to a small piece of work that would not even be worth mentioning – if the way that the problem is being solved wouldn’t be so nice ;).

The reason why I’m writing this is because I have to work on such a task with PostgreSQL, and I already know that it will be quite a tough piece of work, which could be solved with MySQL within seconds. So to everybody, who doesn’t still know the power of the GROUP_CONCAT aggrigate function, here’s a little example, which I think is self-explaining:

mysql> CREATE TABLE group_concat_test (
    ->   id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
    ->   _key INT UNSIGNED NOT NULL,
    ->   _value CHAR(1) NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO group_concat_test
    ->    (_key, _value) VALUES
    ->    (1, 'b'),
    ->    (1, 'c'),
    ->    (2, 'a'),
    ->    (2, 'd'),
    ->    (2, 'c'),
    ->    (3, 'b'),
    ->    (3, 'e');
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT _key,
    ->        COUNT(*) as cnt,
    ->        GROUP_CONCAT(_value ORDER BY _value SEPARATOR ', ')
    ->          as value_list
    ->   FROM group_concat_test
    ->   GROUP BY _key
    ->   ORDER BY _key;
+------+-----+------------+
| _key | cnt | value_list |
+------+-----+------------+
|    1 |   2 | b, c       |
|    2 |   3 | a, c, d    |
|    3 |   2 | b, e       |
+------+-----+------------+
3 rows in set (0.00 sec)

mysql>

The GROUP_CONCAT function is available since MySQL 4.1, so for everyone still running an older version, this would be one more (of many) reason to update.

2 thoughts on “Hail to GROUP_CONCAT!”

  1. if I were to search for “b” and “c” in your value list column, all 3 rows will be returned. How can I filter it to make it return the row with “b” and “c” only. i.e. row 1.
    Thanks a lot

  2. This self join should do the trick:

    SELECT a.id, a._key, a._value, b._value
    FROM group_concat_test a INNER JOIN group_concat_test b
      ON a._key = b._key
    WHERE a._value = 'b'
      AND b._value = 'c'

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.