Analytics Report Creation Error

[Nchoudhary]

Hi All,

I encountered an error while creating an Analytics report "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';1) as result' at line 1"

Query:

select * from
(select u.username as username,
count(ua.userkey) as AD_Accounts
u.DISPLAYNAME ,
u.email ,
u.STATUSKEY,
a.name
from users u,
user_accounts ua,
accounts a
where u.userkey = ua.userkey
and u.statuskey =1
and a.status = 1
and ua.ACCOUNTKEY = a.ACCOUNTKEY
and a.ENDPOINTKEY in (select endpointkey from endpoints where endpointname = 'Active Directory EndPoint') GROUP BY ua.userkey) as q where q.AD_Accounts>1

Reaching out to seek assistance in identifying and rectifying the issue.

[CR · Accepted solution]

try below

select * from
(select u.username as username,
count(ua.userkey) as AD_Accounts,
u.DISPLAYNAME ,
u.email ,
u.STATUSKEY as statea,
a.name
from users u,
user_accounts ua,
accounts a
where u.userkey = ua.userkey
and u.statuskey =1
and a.status = 1
and ua.ACCOUNTKEY = a.ACCOUNTKEY
and a.ENDPOINTKEY in (select endpointkey from endpoints where endpointname = 'Active Directory EndPoint') GROUP BY ua.userkey) as q where q.AD_Accounts>1

[naveenss · Accepted solution]

There was a typo in your query. Try below query

SELECT 
    *
FROM
    (SELECT 
        u.username AS username,
            COUNT(ua.userkey) AS AD_Accounts,
            u.DISPLAYNAME,
            u.email,
            u.STATUSKEY,
            a.name
    FROM
        users u, user_accounts ua, accounts a
    WHERE
        u.userkey = ua.userkey
            AND u.statuskey = 1
            AND a.status = 1
            AND ua.ACCOUNTKEY = a.ACCOUNTKEY
            AND a.ENDPOINTKEY IN (SELECT 
                endpointkey
            FROM
                endpoints
            WHERE
                endpointname = 'Active Directory EndPoint')
    GROUP BY ua.userkey) AS q
WHERE
    q.AD_Accounts > 1