How to get distinct values using group_concat() in sql

Question

I want all the distinct courier codes for that particular month,state and city in column with ',' as separator. I tried group_concat() but the codes are repeating.

Sample table

Month	City	State	Pincode	Courier	Total_orders
1	City1	State1	110021	DTDC	20000
1	City1	State1	110021	GA	30000
2	City1	State1	110021	DTDC	10000
1	City2	State2	110029	DTDC	25000
1	City2	State2	110029	DTDC	20000
1	City2	State2	110029	DTDC	15000

select distinct Pincode, Month, City, State, 
sum(Total_orders) as Total_orders, 
group_concat(Courier) as CourierType
from table1
group by Pincode, Month

Output:

Month	City	State	Pincode	CourierType	Total_orders
1	City1	State1	110021	DTDC,GA	50000
2	City1	State1	110021	DTDC	10000
1	City2	State2	110029	DTDC,DTDC,DTDC	60000

But is there any way I can get only distinct codes instead of 'DTDC,DTDC,DTDC'? For a huge data set,this column with repetitive courier types is getting messy.

Desired output:

Month	City	State	Pincode	CourierType	Total_orders
1	City1	State1	110021	DTDC,GA	50000
2	City1	State1	110021	DTDC	10000
1	City2	State2	110029	DTDC	60000

score 1 · Answer 1 · answered May 30 '21 at 15:09

I think you just want group_concat(distinct) with the right group by columns:

select Pincode, Month, City, State, 
       sum(Total_orders) as Total_orders, 
       group_concat(distinct Courier) as CourierType
from table1
group by Pincode, Month, City, State

How to get distinct values using group_concat() in sql

1 Answers1