1

I have created an autoamtion that pulls the data from Data View and saved the records in a data extension (My_Sub_DE) using the following sql query.

SELECT *
FROM ent._Subscribers WITH (NOLOCK)

Now, that I have all records in the data extension (My_Sub_DE), I am using the following SQL query to pull top 10 domain names and saving it in another data extension (My_Sub_DE_DomainsStats)

select top 10 Domain, count(*) AS Count
from My_Sub_DE
group by Domain
order by Count desc

My data extension - My_Sub_DE_DomainsStats, includes these 3 columns - Domain - Count - Percent

Currently, I am getting the top 10 domains with count based on my above (2nd) sql query but I want to calculate the percentage also.

Can anyone please help with the SQL query?

This is what I have created so far but I am getting error - Incorrect syntax near the keyword 'Percent'.

select top 10 Domain, count(*) AS Count, (count(*)*100 / (SELECT COUNT(*) FROM ent._Subscribers WITH (NOLOCK))) AS Percent
from My_Sub_DE
group by Domain
order by Count desc

1 Answer 1

1

The issue is that 'percent' is a SQL function, so it is causing validation issues. Simplest solution is to name it something else - e.g. (ofTotal) and it should work fine.

10
  • A new issue that I am facing is that, the calculated ofTotal...if any ofTotal is less than 1%, it is coming as 0 in my data extension (My_Sub_DE_DomainsStats) and in addition to that I see the calculated percent in even numbers only, nothing is showing up as decimal value. The ofTotal data type is already set as Decimal 18,3. Any advice?
    – kuml
    May 24, 2017 at 11:56
  • I would use: CONVERT(decimal(5,2),(CONVERT(float,count())/CONVERT(float,(SELECT COUNT() FROM ent._Subscribers))*100)) AS ofTotal May 24, 2017 at 13:00
  • I am not sure if you forgot * with count so I added in my query but getting error - The query syntax is invalid Incorrect syntax near '?'.
    – kuml
    May 24, 2017 at 13:41
  • the comments seemed to have removed some of my formatting. I will try again CONVERT(decimal(5,2),(CONVERT(float,count(*))/CONVERT(float,(SELECT COUNT(*) FROM _Subscribers))*100)) + '%' AS ofTotal May 24, 2017 at 14:08
  • Still getting the same error - The query syntax is invalid Incorrect syntax near '?'. It is strange because there is no "?" in the query. Just curious. would you like me to keep data datatype of ofTotal as Text or something else?
    – kuml
    May 24, 2017 at 15:09

You must log in to answer this question.

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