Trying to remove duplicate rows if only column is different-CodePudding

I have table real_estate as

name       lat      long    room

house1   26.0351   81.782   3
house1   26.0351   81.782   4
house1   26.0351   81.782   2
house1   26.0351   81.782   2
house2   26.1733   81.7132  2

I want to show it as

name     lat       long    room


house1   26.0351   81.782  [2,3,4]
house2   26.1733   81.7132  [2]

i don't specifically want room to be in array, more like concatenated.

I tried using

select name, concat(room,',',room) from `real_estate` group by lat,long;

But, it is only concatenating duplicate items.

sorry for confusion, i just started learning sql

CodePudding user response：

You want GROUP_CONCAT:

select name, lat, long, group_concat(distinct room order by room) as rooms
from real_estate
group by name, lat, long
order by name, lat, long;