Count distinct values

Counting distincts with precision

Want to run a uniqueness check? That's where DISTINCT becomes your best friend. To count distinct values across more than one column, you can combine them like so:

-- I mean, who said column1 and column2 can't be best friends, right?
SELECT COUNT(DISTINCT CONCAT(column1, column2)) FROM table;


Grouping unique values

To get a clear visual of the distribution of unique values, good old GROUP BY comes into play:

-- unique distribution is like attending a party and making sure not to miss anything!
SELECT column, COUNT(DISTINCT another_column) 
FROM table 
GROUP BY column;


This gives us the unique counts of another_column for each distinct value present in column.

Up the performance game with subqueries

Who said sorting out distinct values has to be hard? Use subqueries for smoother operations:

-- Like moving furniture, where you clean it and arrange it back neatly!
SELECT COUNT(*) FROM (SELECT DISTINCT column FROM table) AS subquery;


This subquery first finds the distinct values, then the main query counts them, all in one go!

Present distinct counts

Exciting, isn't it? Now, by using SELECT count(column) AS count, column, we can display distinct counts along with the values:

-- By displaying the results, it's like inviting them over for dinner and introducing them one by one!
SELECT customer_id, COUNT(DISTINCT pet_id) AS distinct_pets FROM customers_pets GROUP BY customer_id;


No assumptions, only clarity

When data schemas are cryptic or requirements aren't clear, don't guess. Then it's time to seek clarity, ensuring the SQL query aligns perfectly with the data.

Visual aid for clarity

Visual formats, such as a simple table, often provide more context:

-- Think of it as playing with Lego blocks and building what you visualize!
SELECT customer, COUNT(DISTINCT pet) AS distinct_pets
FROM ownerships
GROUP BY customer;


Statistical measure alongside counting

Sometimes, you need more than just the count. Like an average too:

-- Having the average on the table, it's like having the cake and eating it too!
SELECT ROUND(AVG(DISTINCT column), 2) AS avg_distinct
FROM table;


This gives you the average of distinct values in the column, rounded to two decimal places.