Grouping in Mysql-CodePudding

i need to get the top touristCount in each month like January Zambia has 4 touristCount i need to select only Zambia for January and so on



    user
     `useri_id` |  `username`   | `email` | `nationality`
      1             Joseph           ``       US
      2             Abraham.         ``       UK
      3             g.wood           ''       Zambia
      4             Messi.           ''       France
      5             Ronaldo.         ''       Namibia
      6             Pogba.           ''       Holand.

    bookings
       booking_id   |  user_id   | booking_date | tour_id
        1                1           2022-01-01       1
        2                1           2022-01-01       6
        3                1           2022-05-01       2
        4                3           2022-01-01       5
        5                2           2022-04-01       5
        6                2           2022-11-01       7
        7                3           2022-12-01       2
        8                6           2022-01-01       1

this is what i have tried

SELECT s.nationality AS Nationality,
COUNT(b.tourist_id) AS touristsCount,
MONTH(STR_TO_DATE(b.booked_date, '%d-%m-%Y')) AS `MonthNumber`
FROM bookings b, users s
WHERE s.user_id = b.tourist_id
AND YEAR(STR_TO_DATE(b.booked_date, '%d-%m-%Y')) = '2022'
GROUP BY Nationality,MonthNumber
order BY MonthNumber ASC
LIMIT 100

i need the results to be like

nationality    |     TouritIdCount   |      MonthNumber
  US                       2                     01
  UK                       1                     04
  US                       1                     05
  UK                       1                     11
  ZAMBIA                   1                     12

CodePudding user response：

Try this :

SELECT nationality, COUNT(booking_id) AS TouristIdCount, MONTH(booking_date) AS MonthNumber
FROM users u
JOIN bookings b ON u.user_id = b.user_id
WHERE YEAR(booking_date) = 2022
GROUP BY nationality, MonthNumber
ORDER BY TouristIdCount DESC, MonthNumber ASC

CodePudding user response：

you can use

having COUNT(b.tourist_id) >= 2