SQL Server Performance

Distinct Count

Discussion in 'SQL Server Reporting Services' started by jastone, Sep 8, 2005.

  1. jastone New Member

    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
  2. ranjitjain New Member

    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=':)' />]
  3. Adriaan New Member

    SELECT col1, col2
    FROM MyTable
    UNION ALL
    SELECT COUNT(DISTINCT col1), COUNT(DISTINCT col2)
    FROM MyTable
  4. Adriaan New Member

    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
  5. jastone New Member

    problem is: this table is populated from a stored procedure

    Joe Janka
  6. ranjitjain New Member

    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()
  7. Adriaan New Member

    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?!

Share This Page