By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH table_visitor AS (
SELECT 1 AS id, 'london' AS city, 'uk' AS country, '2017-11-02 13:01:00' AS time_visit UNION ALL
SELECT 2, 'new york', 'usa', '2017-11-13 00:23:00' UNION ALL
SELECT 3, 'london', 'uk', '2017-11-17 10:23:00' UNION ALL
SELECT 4, 'bristol', 'uk', '2017-11-22 07:00:00' UNION ALL
SELECT 5, 'texas', 'usa', '2017-11-30 04:10:00' UNION ALL
SELECT 6, 'dublin', 'ireland', '2017-12-02 13:01:00' UNION ALL
SELECT 7, 'new york', 'usa', '2017-12-13 00:23:00' UNION ALL
SELECT 8, 'london', 'uk', '2017-12-17 10:23:00' UNION ALL
SELECT 9, 'bristol', 'uk', '2017-12-22 07:00:00' UNION ALL
SELECT 10, 'london', 'uk', '2018-01-01 10:00:00' UNION ALL
SELECT 11, 'washington', 'usa', '2018-01-13 14:00:00'
)
SELECT
t1.year,
t1.month,
t1.cities,
t2.countries
FROM
(
SELECT
year,
month,
GROUP_CONCAT(t.city_label, '(', CAST(t.cnt AS CHAR(50)), ')') cities
FROM
(
SELECT
YEAR(time_visit) year,
MONTH(time_visit) month,
CONCAT(country, '-', city) AS city_label,
COUNT(*) AS cnt
FROM table_visitor
GROUP BY YEAR(time_visit), MONTH(time_visit), CONCAT(country, '-', city)
) t
GROUP BY year, month
| year | month | cities | countries |
|---|---|---|---|
| 2017 | 11 | usa-new york(1),uk-bristol(1),usa-texas(1),uk-london(2) | uk(3),usa(2) |
| 2017 | 12 | ireland-dublin(1),usa-new york(1),uk-london(1),uk-bristol(1) | usa(1),uk(2),ireland(1) |
| 2018 | 1 | usa-washington(1),uk-london(1) | uk(1),usa(1) |