Sun Z Sun Z - 11 days ago 6
MySQL Question

Use conditions in GROUP_CONCAT

I have the following database:

+-------+-------------+---------+
| id | Name | Quantity|
+-------+-------------+---------+
| 1 | Anna | 0 |
| 2 | John | 5 |
| 3 | Anna | 3 |
+-------+-------------+------+


I am using this query:

SELECT name, GROUP_CONCAT(DISTINCT quatity SEPARATOR ', ')
FROM table
GROUP BY name;


As results I got this:


Anna: 0, 3

John: 5


However I want something like this:


Anna: 3 (I don't want to show quantity = 0)

John: 5


I tried this, but it didn't work:

GROUP_CONCAT(DISTINCT
CASE
WHEN quantity > 0 THEN name
else null
END
ORDER BY name ASC SEPARATOR ', ')

Answer

Someone suggested this but I'll put it in an answer:

SELECT id, name, GROUP_CONCAT(DISTINCT quantity SEPARATOR ', ')
FROM table
WHERE quantity > 0
GROUP BY id, name;

Also, you seem to have some typos with quantity* and id* instead of article number, at least according to your table. Plus, I recommend putting both id and name in the select statement and the group by statement.

Comments