I’m working with a database table and need to figure out how many unique entries exist in a specific field. I know I can retrieve all the unique values using these methods:
SELECT DISTINCT product_type FROM inventory;
SELECT product_type FROM inventory GROUP BY product_type;
However, I don’t want to see all the individual values. I just want to get a count of how many different unique values there are. Do I need to wrap this in a subquery to get the total count, or is there a more direct approach?
Oh nice! COUNT(DISTINCT) works great here, but I’m wondering - do you see any performace hits with massive tables? And does it treat nulls the same as regular COUNT?
COUNT(DISTINCT column_name) is definitely the most straightforward solution. I’ve used this tons in production and it handles NULL values perfectly by excluding them, which is what you want 99% of the time. Just make sure you’ve got proper indexing on whatever column you’re counting if you’re dealing with huge datasets. COUNT(DISTINCT) performs pretty well even on tables with millions of rows, but execution time varies a lot depending on your database engine and whether that column’s indexed. For PostgreSQL and MySQL, performance has been solid for most business cases I’ve worked with.