1

The following query keeps throwing up error messages (different each time). Can anyone see something wrong with it? The query is designed to power an email sent to internal stakeholders letting them know how many times their emails to clients have been opened and clicked by region and customer type.

There's a data extension for the sending segment, which relates to the second data extension containing the figures on internal stakeholder name and this second data extension relates to the third data extension on email name.

We also need to first convert some text values (as the opens and clicks are stored in data extension 2 as text) to numbers, then add these values (opens and clicks).

The idea is to end up with two rows per internal stakeholder - one listing the open and click data by region and the other by customer type. Any assistance would be greatly appreciated.

Select 
Convert(INT, d2.[Number of Unique Opens]) as d2.Actual_Number_Unique_Opens,
Convert(INT, d2.[Number of Unique Clicks]) as 
d2.Actual_Number_Unique_Clicks,

d1.PM,
d1.[Contact ID],
d3.[Contact: region],
null as [Contact: Customer Type],
sum (d2.[Number of Unique Opens]),
sum (d2.[Number of Unique Clicks])

from PMOwnerMapping_Config d1
inner join [Emails sent last 30 days PMs] d2 on (d2.[PM Validator] = d1.PM)
inner join [Sends by type and region] d3 on (d3.[Individual Email Result: 
Email Name] = d2.[Email Name])
group by 
d1.PM,
d1.[Contact ID],
d3.[Contact: region]

union

select

d1.PM,
d1.[Contact ID],
d3.[Contact: Customer Type],
null as [Contact: region],
sum (d2.[Number of Unique Opens]),
sum (d2.[Number of Unique Clicks])

from PMOwnerMapping_Config d1
inner join [Emails sent last 30 days PMs] d2 on (d2.[PM Validator] = d1.PM)
inner join [Sends by type and region] d3 on (d3.[Individual Email Result: 
Email Name] = d2.[Email Name])
group by 
d1.PM,
d1.[Contact ID],
d3.[Contact: Customer Type]
  • 1
    Also, both selects in the union must have the same columns. Please update your question with the desired output, because it's really not clear what you're trying to get. – Adam Spriggs Aug 11 '17 at 15:10
  • I agree with @AdamSpriggs, we need to see a desired output – thechrishaddad Aug 11 '17 at 15:10
1

So its a bit hard without the data, but I noticed a few errors:

  1. You are defining table alias's on alias's which is wrong.
  2. You are not defining alias's for the SUM() functions.
  3. Instead of saying NULL AS ALIAS, you can just do '' AS ALIAS
  4. Cleaned up some of your syntax overall.

Let me know if this works for you.

SELECT 
CONVERT(INT, d2.[Number of Unique Opens]) AS Actual_Number_Unique_Opens,
CONVERT(INT, d2.[Number of Unique Clicks]) AS Actual_Number_Unique_Clicks,

d1.PM,
d1.[Contact ID],
d3.[Contact: region],
'' AS [Contact: Customer Type],

SUM(d2.[Number of Unique Opens]) AS NumUniqueOpens,
SUM(d2.[Number of Unique Clicks]) AS NumUniqueClicks

FROM PMOwnerMapping_Config d1
INNER JOIN [Emails sent last 30 days PMs] d2 
ON d2.[PM Validator] = d1.PM
INNER JOIN [Sends by type and region] d3 
ON d3.[Individual Email Result: Email Name] = d2.[Email Name]
GROUP BY 
d1.PM,
d1.[Contact ID],
d3.[Contact: region]

UNION

SELECT

d1.PM,
d1.[Contact ID],
d3.[Contact: Customer Type],
'' AS [Contact: region],
SUM(d2.[Number of Unique Opens]) AS NumUniqueOpens,
SUM(d2.[Number of Unique Clicks]) AS NumUniqueClicks

FROM PMOwnerMapping_Config d1
INNER JOIN [Emails sent last 30 days PMs] d2 
ON d2.[PM Validator] = d1.PM
INNER JOIN [Sends by type and region] d3 
ON d3.[Individual Email Result: Email Name] = d2.[Email Name]
GROUP BY 
d1.PM,
d1.[Contact ID],
d3.[Contact: Customer Type]
  • From thechrishaddad's query, I now supposedly have an error near 'from' according to Marketing Cloud. The data extension names are all correct. – Antonio Aug 11 '17 at 15:05
  • 1
    remove the extra comma after NumUniqueClicks – Adam Spriggs Aug 11 '17 at 15:07
  • Now get "Emails sent last 30 days PMs.Number of Unique Opens' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" and if I add to group by clause, I get "Operand data type nvarchar is invalid for sum operator" – Antonio Aug 11 '17 at 15:11
  • I removed the extra comma in my answer – thechrishaddad Aug 11 '17 at 15:12
  • @Antonio, you need to CAST(INT) that column because you are trying to SUM a field that is NVARCHAR which does not make sense – thechrishaddad Aug 11 '17 at 15:13
0

You're doubling-up on your d2. aliases in the first two columns.

SELECT 
  d1.PM
, d1.[Contact ID]
, d3.[Contact: region]
, '' AS [Contact: Customer Type]
, SUM(d2.[Number of Unique Opens]) AS NumUniqueOpens
, SUM(d2.[Number of Unique Clicks]) AS NumUniqueClicks
FROM PMOwnerMapping_Config d1
INNER JOIN [Emails sent last 30 days PMs] d2 ON d2.[PM Validator] = d1.PM
INNER JOIN [Sends by type and region] d3 ON d3.[Individual Email Result: Email Name] = d2.[Email Name]
GROUP BY 
d1.PM
, d1.[Contact ID]
, d3.[Contact: region]

UNION

SELECT
  d1.PM
, d1.[Contact ID]
, d3.[Contact: Customer Type]
, '' AS [Contact: region]
, SUM(d2.[Number of Unique Opens]) AS NumUniqueOpens
, SUM(d2.[Number of Unique Clicks]) AS NumUniqueClicks
FROM PMOwnerMapping_Config d1
INNER JOIN [Emails sent last 30 days PMs] d2 ON d2.[PM Validator] = d1.PM
INNER JOIN [Sends by type and region] d3 ON d3.[Individual Email Result: Email Name] = d2.[Email Name]
GROUP BY 
d1.PM
, d1.[Contact ID]
, d3.[Contact: Customer Type]
  • The error now is "Emails sent last 30 days PMs.Number of Unique Opens' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" – Antonio Aug 11 '17 at 14:59
  • removed the first two column in the first select. this is where you need to start. – Adam Spriggs Aug 11 '17 at 15:11
  • From your initial query, it looks like 'Number of Unique Opens' needs to be convert to INT. If the datatype is non-numeric, then this conversion needs needs to be done in a subquery before the data can be used in a Sum() aggregate function. – David Devoy Aug 11 '17 at 17: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.