0

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

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
| improve this answer | |
  • Hi @Macca and thank you for your contribution. I ran your SQL Query but I didn't get any errors, nor did it actually populate the bookings count as intended. Any ideas? – Foweyboy Feb 25 at 2:37
  • Are there any records that match on email address between the two tables? The SQL's not very complicated. Is the field "Bookings_Count" above spelled correctly and exists iin the XYZ Customers table? – Macca Feb 25 at 2:43
  • Hi Macca, thanks for your help again - double checked the column and DE names - there was a mismatch! However now that the data is populating there is an issue that instead of populating a 0 for Customers that have 0 bookings they get a 1 count anyway. The count is correct if you have 1, 2, 3 bookings etc, but those without any booking are showing as 1 instead of 0. How can this be corrected please? – Foweyboy Feb 25 at 23:31
  • I've updated the code. Do you have a booking id, or similar in your bookings data? – Macca Feb 25 at 23:35
  • Hi Macca, thanks for the updated code, worked perfectly. Yes there is an ID for the booking which is the primary key in the booking table - why do you ask? – Foweyboy Feb 29 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.