Customer and Bookings DE - Save COUNT of Bookings in Customer DE?

Question

I have 2 De:

• XYZ Customers
• XYZ Bookings

Within both DE there is an email field with the below names:

• XYZ Customers email field is called 'EmailAddress'
• XYZ Bookings email field is called 'Cust_Email'

I have a Bookings_Count (number) field in the XYZ Customers DE.

I want to run an SQL Query Activity to count the number of Bookings that match against the customers Email address in the XYZ Bookings DE and save this into the XYZ Customers.Bookings_Count field.

This is my solution:

select count (Cust_Email) as [XYZ Customers.BookingCount]
  from [XYZ Bookings]
  where [XYZ Customers.EmailAddress] = [XYZ Bookings.Cust_Email]

but I get the below validation error:

An error occurred while checking the query syntax. Errors: Invalid column name 'XYZ Customers.EmailAddress'.

Any help greatly appreciated!

FYI: The column name

• XYZ Customers.EmailAddress

is perfectly valid and is in use in other queries - I think the error message is because this table isn't previously in use in this query?

Do I need to approach this differently?

Update:

I got the code to validate in the below format:

 select  count (Cust_Email) as [XYZ Customers.BookingCount],
    Cust_Email as [XYZ Customers.EmailAddress]   
    from [XYZ Bookings] LEFT JOIN [XYZ Customers]
    ON [XYZ Bookings].Cust_Email = [XYZ Customers].EmailAddress
    Group by Cust_Email

but it wouldn't update the DE as it wouldn't use the email to match the row to update - not sure how to fix that?

I managed to find a work around but I'm keen to see if a better solution exists.

My work around was to:

1. Create an intermediate DE with just 2 columns, bookings & cust_email.

2. Made an SQL query activity to save the total booking count & email address into the intermediate DE

   select count (Cust_Email) as bookings, Cust_Email from [XYZ Bookings] WHERE Cust_Email IS NOT NULL Group by Cust_Email

3. Created a second query activity to join the booking count data to the original DE matching on email address.

   Select Bookings as BookingCount, Cust_Email as EmailAddress from [Booking Totals] as B LEFT JOIN [XYZ Customers] as C ON C.EmailAddress = B.Cust_Email

Hope someone can show me a better solution that does it all in one query and without the need for an intermediate DE

Thanks for reading

Matt

Answer 1

There are a bunch of issues with your use of column alias, escaping of table and field names and joins. I think this is what you're looking for:

SELECT
    c.EmailAddress,
    COUNT(b.Cust_Email) as Bookings_Count
FROM
    [XYZ Customers] c LEFT JOIN
    [XYZ Bookings] b
        ON c.EmailAddress = b.Cust_Email
GROUP BY
    c.EmailAddress