MySQL - count and group by - 在一行中显示所有结果

Question

我认为这应该很简单，但我无法弄清楚或在这里找到解决方案。

我有一个表 ITEM_PROPERTIES

item_ID int
property_name char(20)
property_value char(20)

示例数据

5   Colour  Black
5   Size    M   
6   Colour  Blue
6   Size    L   
7   Colour  Purple
7   Size    M   
8   Colour  Blue
8   Size    S   
9   Colour  Wine
9   Size    M   

如果我这样做:

SELECT property_value, count(property_value) 
FROM ITEM_PROPERTIES where property_name='Colour'
group by property_value

我明白了:

Black   1
Blue    2
Purple  1
Wine    1

我怎样才能把它放在一行中，比如:

Black 1, Blue 2, Purple 1, Wine 1

我试过了:

SELECT group_concat(property_value, count(property_value)) 
FROM ITEM_PROPERTIES where property_name='Colour'
group by property_value

SELECT group_concat(concat(property_value, count(property_value))) 
FROM ITEM_PROPERTIES where property_name='Colour'
group by property_value

但是都报错 Invalid use of group function

Answer 1

select group_concat(concat(property_value, '  ', cnt))
from (
    SELECT property_value, count(property_value) as cnt
     FROM ITEM_PROPERTIES
     WHERE property_name = 'Colour'
     GROUP BY property_value
) c