I have a table:

source, duration 84991234567, 21 84991234568, 29 84951234569, 22

Where source is the outgoing telephone number, duration is the duration of the call.

I need to group this data by city code, i.e. get the following:

 8499, 50 8495, 22

where the second number is the sum of the duration.

The main question is how to group by city code?

    2 answers 2

    Could be so:

     select left(source,4), sum(duration) from table group by left(source,4)

      Use the SUBSTRING() function, and make a query with a subquery. As a result, there will be something like (I could be mistaken, because I am writing without checking):

       SELECT city_code, SUM(duration) FROM (SELECT SUBSTRING(source, 1, 4) city_code, duration FROM your_table) GROUP BY city_code;
      • Thanks for the working version and for reminding about SUBSTRING, but the option with LEFT and without a subquery seemed to me better. - kezman 6:49 pm