KevInSol KevInSol - 7 months ago 5
SQL Question

MySQL - count and group by - display all results in one row

I think this should be simple, but I can't figure it out or find a solution here.

I have a table ITEM_PROPERTIES

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


Sample data

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


If I do:

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


I get :

Black 1
Blue 2
Purple 1
Wine 1


How can I get this on one row like:

Black 1, Blue 2, Purple 1, Wine 1


I tried :

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


But both give an error Invalid use of group function

Answer
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
Comments