Retrieve Count with Multiple Rows in SQL

If you are querying a database and also want to display the count of the items in the table, you might run into this issue.

If you query a table like this: select distinct title, uniqueid from table

You might retrieve the count like this: select count(distinct title) from table

However, the count you retrieve might be different from the number of rows you retrieved from the actual table. That is because the distinct title, uniqueid is retrieving two rows that are distinct, and the count is retrieving one row that is distinct. If the distinct title is different from the distinct title, uniqueid, then your count would be wrong.

You could write the count query like this to get the actual number of rows.

SELECT count(distinct title+CONVERT(varchar(2000),uniqueid)) FROM table

This query will retrieve the count of the two rows assuming that the uniqueid is actually unique. This will work to get you an actual count.