SELECT g.name, COUNT(b.idbooking) FROM guests AS g LEFT OUTER JOIN bookings AS b ON b.idpax1 = g.idpax OR b.idpax2 = g.idpax OR b.idpax3 = g.idpax OR b.idpax4 = g.idpax GROUP BY g.name;
I suggest you rethink your structure. Instead of two tables, use three:
Guest
Booking
— id
Booking Guest
— Booking id
— Guest id
You can use triggers or code to limit it to 4 guests per booking if you want, a Unique check on Booking Guest to make sure the same guest is not listed twice for the same booking, and your calculations and groupings will be much easier.