Syntax error in query

Question

I have written this query in Automation Studio SQL query activity to retrieve data for unique opens in last 30 days. However i am getting this syntax error while trying to validate. Not sure why

An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'Values'

SELECT p.Subscriberkey, q.count AS Values 
FROM _Subscribers p
LEFT JOIN (
    SELECT 
    Subscriberkey,
    Count(*) as 'count'
    FROM _Open
    where IsUnique = 'True'
    and EventDate > DateAdd(Day, -30, Getdate())
    Group by Subscriberkey
) q on p.Subscriberkey = q.Subscriberkey

Can someone help, please?

Answer 1

The word 'Values' as an alias, is a reserved keyword in SQL. You can use another name as an alias or you can enclose 'Values' in square brackets to use it as an identifier.

Query::

SELECT p.Subscriberkey, q.[count] AS [Values] 
FROM _Subscribers p 
LEFT JOIN ( 
           SELECT  Subscriberkey, COUNT(*) as [count] 
           FROM _Open 
           WHERE IsUnique = 'True' 
           AND EventDate > DATEADD(DAY, -30, GETDATE()) 
           GROUP BY Subscriberkey ) 
           q ON p.Subscriberkey = q.Subscriberkey

I have enclosed 'count' and 'Values' in square brackets. This should resolve the syntax error you were facing. Try validating this query in your SQL activity.