Distinct Count

at the bottom of one of my columns, i have want to have a count of the distinct values in that column, what is the syntax to make that happen? thanks in advance!

Joe Janka

Try union operator.<br /><br />select col1 from table1<br />union<br />select count(DISTINCT col1) from table1[<img src='/community/emoticons/emotion-1.gif' alt='' />]

SELECT col1, col2
FROM MyTable
UNION ALL
SELECT COUNT(DISTINCT col1), COUNT(DISTINCT col2)
FROM MyTable

Note that if the column is not numeric, you have to CAST the result:

SELECT col1, col2
FROM MyTable
UNION ALL
SELECT CAST(COUNT(DISTINCT col1) AS VARCHAR), CAST(COUNT(DISTINCT col2) AS VARCHAR)
FROM MyTable

problem is: this table is populated from a stored procedure

Joe Janka

Also make sure you have selected same number of columns in first query i.e. before union
and after union i.e. second select as you only need count for just one column and for rest columns you can try
select '','',count()

quote:Originally posted by jastone

problem is: this table is populated from a stored procedure

Joe Janka

The data is in a table, you have a query that adds the counts at the end of the resultset. I can't see how a stored procedure that has finished before this can cause a problem at this stage?!