I was playing with some statistics and wanted to display some queries as a single, comma-delimited text.

Why one may need this? Since the result of the queries can be concatenated in an endless ways by (eg. PHP) code… The answer: to have one-line query returning directly the string – eg. for statistical purposes only.

The data

Consider any type of data, for example:

CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_bin NOT NULL,
`color` varchar(20) COLLATE utf8_bin NOT NULL,
`quantity` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`));

Insert some data…

INSERT INTO `test` (`id`, `name`, `color`, `quantity`) VALUES
(1, 'Orange', 'orange', 5),
(2, 'Apple', 'red', 25),
(3, 'Strawberry', 'red', 15),
(4, 'Banana', 'yellow', 12),
(5, 'Lemon', 'yellow', 1),
(6, 'Raspberry', 'red', 15),
(7, 'Raspberry', 'red', 15),
(8, 'Plum', 'blue', 5),
(9, 'Blueberry', 'blue', 25),
(10, 'Blue grapes', 'blue', 1),
(11, 'Grapes', 'green', 1),
(12, 'Kiwi', 'green', 5);

The solution: MySQL GROUP_CONCAT

To return any query as string, use the GROUP_CONCAT():

SELECT GROUP_CONCAT(`name`) FROM `test`
>> Orange,Apple,Strawberry,Banana,Lemon,Raspberry,Raspberry,Plum,Blueberry,Blue grapes,Grapes,Kiwi

To alter the order of the results:

Doesn't work:
SELECT GROUP_CONCAT(`name`) FROM `test` ORDER BY `name`;
>> Orange,Apple,Strawberry,Banana,Lemon,Raspberry,Raspberry,Plum,Blueberry,Blue grapes,Grapes,Kiwi

Does work:
SELECT GROUP_CONCAT(`name` ORDER BY `name`) FROM `test`;
>> Apple,Banana,Blue grapes,Blueberry,Grapes,Kiwi,Lemon,Orange,Plum,Raspberry,Raspberry,Strawberry

To enumerate distinct values:

SELECT GROUP_CONCAT(DISTINCT `color`) FROM `test`;
>> orange,red,yellow,blue,green

Ordering works like this:

SELECT GROUP_CONCAT(DISTINCT `color` ORDER BY `color`) FROM `test`;
>> blue,green,orange,red,yellow

Filtering

The WHERE clause must be specified at the end of the query, not inside the GROUP_CONCAT():

SELECT GROUP_CONCAT(DISTINCT `color`) FROM `test` WHERE `quantity`>10;
>> red,yellow,blue

Specify a separator

For practical purposes, you may want a different separator. Use the SEPARATOR clause inside the GROUP_CONCAT(). Notice the spaces around the dashes:

SELECT GROUP_CONCAT(DISTINCT `color` SEPARATOR ' - ') FROM `test` WHERE `quantity`>10;
>> red - yellow - blue

Documentation

You may want to read more about GROUP_CONCAT()… Visit the official documentation page at dev.mysql.com.

I have two tables, “products” and “brands”. In the “products” table, the product’s name is in the “name” field. The product’s brand is represented by the # (diez) character. To know which is the brand, there’s a “brand_id” field.

So far we have:

products table:
  id: int
  name: varchar
  brand_id: int

brands table:
  id: int
  name: varchar

I was thinking in the other day, can I build the name only in SQL? The problem is that not all products have a brand…

The first approach was the REPLACE() function:

function REPLACE(str, from_str, to_str)

Nice, but… There’s always a but… In the documentation it doesn’t states, when to_str is NULL, the result is also NULL! Seems to me illogical, normally I expect the from_str
cleared from str in this case…

So, the query

SELECT REPLACE(name, '#', (SELECT name FROM brands WHERE id = products.brand_id)) FROM products;

results in several NULL rows… This is wrong, since products without brands should be displayed as-is.

What solves the problem, is the IF function:

IF(condition, true_value, false_value)

We use the “brand_id” field as condition, we replace the diez only if brand_id > 0. The final query looks like this:

SELECT IF(brand_id > 0, (REPLACE(name, '#', (SELECT name FROM brands WHERE id = products.brand_id))), name) FROM products;