1

I'm working in MSSQL 2008 on a stored procedure for data profiling. I have a script that returns the distinct values and count of each using the following dynamic SQL:

SELECT @SQL = N'SELECT ' + @FieldName + ', COUNT(*) AS [Frequency] FROM ' + @TableName + ' GROUP BY ' + @FieldName + ' ORDER BY [Frequency] DESC';

I would like to add percentage of each distinct count to that output. I think the technique used here would work for what I'm doing but I can't figure out how to integrate the two. The desired output would show the distinct values, the count of each, and the percentage of each.

Thanks in advance for any help.

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
user183121
  • 13
  • 3

2 Answers2

0

your query should be like

SELECT @SQL = 
N'SELECT ' + 
@FieldName + 
', COUNT(*) AS [Frequency] '+ 
', (COUNT(*)* 100 / (Select Count(*) From '+@TableName+ ')) AS [percentage] ' +
'FROM ' + 
@TableName + 
' GROUP BY ' + 
@FieldName + 
' ORDER BY [Frequency] DESC';

see demo here

DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
0

I wanted to share something I added to the excellent answer from @DhruvJoshi in hopes that it might help someone in the future.

I wanted to have percentages displayed with 2 decimal places and add a percentage sign to the output. Here's what I ended up using:

SELECT @SQL = 
N'SELECT ' + 
@FieldName + 
', COUNT(*) AS [Frequency] '+ 
', CAST(CONVERT(DECIMAL(10,2),(COUNT(*)* 100.0 / (Select Count(*) From '+@TableName+ '))) AS nvarchar) + ''%'' AS [Percent] ' +
'FROM ' + 
@TableName + 
' GROUP BY ' + 
@FieldName + 
' ORDER BY [Frequency] DESC'; 
EXECUTE sp_executesql @SQL;

Hope that helps someone in the future. Thanks again @DhruvJoshi

user183121
  • 13
  • 3