sql - I want to count people according to their cities and put them in three categories as in which city there are more than 5 people -

sql - I want to count people according to their cities and put them in three categories as in which city there are more than 5 people -

i have 2 columns. 1-coloumn (city) 2-(names of people) there many cities mumbai, madras, noida,patna , many people belongs cities.

cities people bombay ranjeet bombay rath bombay raman delhi jeten delhi gag ranchi hhh ranchi hjhilj ranchi jklkjjkl ranchi sa ranchi dasd ranchi das agra cacss agra agra dasd agra agra awd bhubaneswar ds bhubaneswar dfsa bhubaneswar asd bhubaneswar sd bhubaneswar d bhubaneswar

i want query in 3 categories 1,2-5,>5 people i.e count people specific cities , set them above categories e.g in bombay lies 3 peoples comes in 2-5 categories

if understand problem right, query should help you:

select cities, case when count(*) = 1 '1' when count(*) between 2 , 5 '2-5' when count(*) > 5 '5' end category, listagg(people, ',') within grouping (order people) names your_table grouping cities

sql database plsql