We’ve got an application (Act!) that uses MSSQL as it’s backend. There is a dropdown list in there, and we want get a list of all the dropdown values that are in use, and also how many records use each value.

Thus this, SQL snippet does that.

SELECT
    DROPDOWN_FIELDNAME,
    COUNT(*) AS 'NumberOfRecords'
FROM
    TBL_TABLENAME
GROUP BY
    DROPDOWN_FIELDNAME
ORDER BY 'NumberOfRecords' DESC;

Replace the DROPDOWN_FIELDNAME in both places, and replace the TBL_TABLENAME with your table name.

That gave me a result like follows:

Hope you find that as useful as we have.