beginner group by question, syntax related-CodePudding

I have a booking table that holds multiple guests and all the bookings they have made throughout the years.

I am supposed to show, the guestsID and the start year of their bookings and the number of bookings they placed if its greater than 5.

SELECT guestid, YEAR(startdate) AS 'Year' 
FROM BOOKING 
WHERE
GROUP BY
HAVING

How do I get the count of all of the individual guest ids? to show how many times they have made reservations?

CodePudding user response：

You can write this way

SELECT guestid, count(guestid), YEAR(startdate) AS 'Year' 
FROM BOOKING 
WHERE 
GROUP BY guestid, year(Startdate)
HAVING count(guestid) > 5;

A sample data set to filter count >= 2

mysql> select * from 
    -> booking;
 ---------- ------------ ------ 
| guest_id | startdate  | flag |
 ---------- ------------ ------ 
|        1 | 2022-01-02 |    1 |
|        1 | 2021-02-02 |    1 |
|        1 | 2021-03-02 |    1 |
|        2 | 2022-01-02 |    1 |
|        2 | 2020-01-02 |    1 |
|        3 | 2022-01-02 |    1 |
|        4 | 2021-01-02 |    1 |
 ---------- ------------ ------ 
7 rows in set (0.01 sec)

mysql> select guest_id, count(guest_id), year(startdate) from booking group by guest_id, year(startdate);
 ---------- ----------------- ----------------- 
| guest_id | count(guest_id) | year(startdate) |
 ---------- ----------------- ----------------- 
|        1 |               2 |            2021 |
|        1 |               1 |            2022 |
|        2 |               1 |            2020 |
|        2 |               1 |            2022 |
|        3 |               1 |            2022 |
|        4 |               1 |            2021 |
 ---------- ----------------- ----------------- 
6 rows in set (0.01 sec)

mysql> select guest_id, count(guest_id), year(startdate) from booking group by guest_id, year(startdate) having count(guest_id) > 1;
 ---------- ----------------- ----------------- 
| guest_id | count(guest_id) | year(startdate) |
 ---------- ----------------- ----------------- 
|        1 |               2 |            2021 |
 ---------- ----------------- ----------------- 
1 row in set (0.01 sec)

CodePudding user response：

You can use window functions:

SELECT B.guestid, B.count, B.YEAR(startdate) AS 'Year' 
FROM (select B.*,
             count(*) over (partition by guestid) as count  
      from BOOKING B
     ) B
WHERE B.count > 5

Or In other query you can use multiple group by

    SELECT  guestid, COUNT(guestid),  YEAR(startdate) AS 'Year' 
        FROM BOOKING
        GROUP BY  guestid , YEAR(startdate)
        Having COUNT(guestid) > 5