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.

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

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.

  • 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 '17 at 11:56
  • I would use: CONVERT(decimal(5,2),(CONVERT(float,count())/CONVERT(float,(SELECT COUNT() FROM ent._Subscribers))*100)) AS ofTotal – Gortonington May 24 '17 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 '17 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 – Gortonington May 24 '17 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 '17 at 15:09

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.