Question 21724 in Mysql Question posted in
The official MySQL documentation can be found here.

Grouping in Mysql

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
Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    you can use

    having COUNT(b.tourist_id) >= 2
    Login or Signup to reply.
  3. 0

    You want to count bookings per month and tourist’s nationality and then show only the top nationality (or nationalities) per month.

    There are two very similar approaches:

    1. Rank the nationalities’ booking counts per month with RANK and only show the best ranked rows.
    2. Select the top booking count per month and only show rows matching their top count.

    The following query uses the second method. It shows one row per month and top booking nationality. Often there may be excatly one row for a month showing the one top booking nationality, but there may also be months where nationalities tie and share the same top booking count, in which case we see more than one row for a month.

    select year, month, nationality, booking_count
from
(
  select
    year(b.booking_date) as year,
    month(b.booking_date) as month,
    u.nationality,
    count(*) as booking_count,
    max(count(*)) over (partition by year(b.booking_date), month(b.booking_date)) as months_max_booking_count
  from bookings b
  join users u on u.user_id = b.tourist_id
  group by year(b.booking_date), month(b.booking_date), u.nationality
) ranked
where booking_count = months_max_booking_count
order by year, month, nationality;

    As your own sample data doesn’t contain any edge cases, here is some other sample data along with my query’s result and an explanation. (In other words, this is what you should have shown in your request ideally.)

    users

    user_id username email nationality
    1 Joseph [email protected] US
    2 Mary [email protected] US
    3 Abraham [email protected] UK

    bookings

    booking_id user_id booking_date tour_id
    1 1 2022-01-11 1
    2 2 2022-01-11 1
    3 3 2022-01-11 1
    4 3 2022-01-22 2
    5 1 2022-05-01 3
    6 2 2022-05-01 3
    7 1 2022-05-12 4
    8 2 2022-05-12 4
    9 3 2022-05-14 5
    10 3 2022-05-20 6
    11 3 2022-05-27 7

    result

    year month nationality booking_count
    2022 1 UK 2
    2022 1 US 2
    2022 5 US 4

    In January there were two tours, but we are not interested in tours. We see four bookings, two by the Americans, two by the Britsh person. This is a tie, and we show two rows, one for UK and one for US with two bookings each.

    In May there were five tours, but again, we are not interested in tours. There are seven bookings, four by the Americans, three by the Britsh person. So we only show US as the top country with four bookings here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search