mySQL GROUP BY different values as same

Question

I have data which looks like following

ping    Type    Network
24      3G      Telecom
23      3.5G    Tata
37      4G      Voda
48      3G      Tata
51      3.5G    Telecom
26      3G      Telecom
37      4G      Voda
48      3G      Voda

I want to group it in such a way that '3G' and '3.5G' make one group and '4G' makes another group. In my case only 'Voda' is '4G'.

I trying to get output like this

avgping Type    Network
24      3XG     Telecom
23      3XG     Tata
37      4G      Voda
48      3XG     Voda

How can that be done?

Answer 1

Assuming you want to use group by, you can use case in a group by:

select (case when type in ('3G', '3.5G') then '3G' else type end) as nettype,
       count(*)
from data d
group by (case when type in ('3G', '3.5G') then '3G' else type end) ;