I need to know what SQL statement to use in an accommodation database looking for a trip user
The error message is clear enough: the group by clause needs to be consistent with the select. Some databases are smart enough to understand that the name of the customer is functionally dependent on its id, and do not require that you put the name in the group by - but not SQL Server.
Also, you need to count on something that comes from the left joined table if you want 0 for customers without bookings.
Consider:
select c.customer_id, c.customer_name, count(ab.customer_id) as [number of accomm slots]
from customers c
left join accommodation_bookings ab on c.customer_id = ab.customer_id
group by c.customer_id, c.customer_name
I would take one step forward and pre-aggregate in a subquery. This is usually more efficient:
select c.customer_id, c.customer_name, coalesce(ab.cnt, 0) [number of accomm slots]
from customers c
left join (
select customer_id, count(*) cnt
from accommodation_bookings
group by customer_id
) ab on c.customer_id = ab.customer_id
You could also express this with a correlated subquery, or a lateral join:
select c.customer_id, c.customer_name, ab.*
from customers c
outer apply (
select count(*) [number of accomm slots]
from accommodation_bookings ab
where c.customer_id = ab.customer_id
) ab
This would take advantage of an index on accommodation_bookings(customer_id) (which should already be there if you have set up a foreign key).
Note: don't use single quotes for identifiers - they are meant for literal strings. In SQL Server, use the square brackets instead.