1

For my question I am to create a query showing the guest's full name that have made more than 2 bookings. It is to show the (family_name, given_name) in one column under the alias "Guest" AND alias "Count(*)" which what assume, counts the the guest_id from a2_booking (table1) which must be more than 2. The second table, "a2_guest", contains the guest_id (same as in a2_booking), family_name, given_name. What can I do to get the following query:

Guest                                                                                COUNT(*)
-- ------------------------------------------------------------------------------------ ----------
-- Fellows, John                                                                               3
-- Gaye, Anthony                                                                               3
-- Grace, Thomas                                                                               3
-- Marvin, Leon                                                                                4
-- Oslovski, Boris                                                                             3
-- Rivers, Jill                                                                                3
-- Slater, Martin                                                                              3
-- Strettle, George

I only managed to use CONCAT to combine the family_name and given_name to one column on the first try and then I tried to count the guests that had multiple bookings made (but I didn't do the calculation greater than 2 yet).

freedomn-m
  • 27,664
  • 8
  • 35
  • 57
Nicholas Rohrmoser
  • 23
  • 3
  • Welcome to Stackoverflow. Stackoverflow editor has a feature that allows you to use tables. see [here](https://meta.stackexchange.com/questions/356997/new-feature-table-support) – MD Zand Dec 19 '22 at 10:00
  • Please update your question to show the ddl for the source tables, some sample data for those tables and the result you want to achieve based on that sample data. Also add the SQL you’ve managed to write – NickW Dec 19 '22 at 12:41

1 Answers1

1

Apologies all. I managed to find an answer that really helped.

SELECT x.family_name || ', ' || x.given_name AS "Guest", COUNT(y.guest) as 
"COUNT(*)"
FROM a2_guest x JOIN a2_booking y
ON (x.guest_id = y.guest)
GROUP BY x.family_name || ', ' || x.given_name
HAVING COUNT(y.guest) > 2;

I appreciate the help though ;)

Nicholas Rohrmoser
  • 23
  • 3