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]
2
  • 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. Commented Aug 11, 2017 at 15:10
  • I agree with @AdamSpriggs, we need to see a desired output Commented Aug 11, 2017 at 15:10

2 Answers 2

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]
5
  • From thechrishaddad's query, I now supposedly have an error near 'from' according to Marketing Cloud. The data extension names are all correct.
    – Antonio
    Commented Aug 11, 2017 at 15:05
  • 1
    remove the extra comma after NumUniqueClicks Commented Aug 11, 2017 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
    Commented Aug 11, 2017 at 15:11
  • I removed the extra comma in my answer Commented Aug 11, 2017 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 Commented Aug 11, 2017 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]
3
  • 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
    Commented Aug 11, 2017 at 14:59
  • removed the first two column in the first select. this is where you need to start. Commented Aug 11, 2017 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. Commented Aug 11, 2017 at 17:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .