Partition Function COUNT() OVER possible using DISTINCT

sql

prompt-engineering

join

subqueries

byAlex Kataev·Sep 11, 2024

// Because who needs duplicates, right?
WITH CTE AS (
  SELECT DISTINCT key, category FROM table
)
// Over the partition, through the query, to distinct's house we go
SELECT 
  category, COUNT(key) OVER (PARTITION BY category) AS distinct_count
FROM CTE;

The magic of CTE comes into light. Deduplicating before counting provides a compact distinct count per category.

Advanced alternatives

Window of Opportunity with DENSE_RANK

// DENSE_RANK: like your average sports day, but for numbers
WITH Ranked AS (
  SELECT 
    key, 
    category, 
    DENSE_RANK() OVER (PARTITION BY category ORDER BY key) AS dr
  FROM table
  WHERE key IS NOT NULL  // Adds a classy touch of exclusivity
)
// Let the ranking games begin!
SELECT 
  category, 
  MAX(dr) AS distinct_count
FROM Ranked
GROUP BY category;

Aha! The quirky DENSE_RANK() roped in! The highest rank within each partition reflects a distinct count. But beware of Null values, unless they're invited guests at your count party.

SQL Server 2012 - Embracing the DISTINCT

In SQL Server 2012 and later, you can finally take your date COUNT(DISTINCT) to an OVER() function.

SELECT 
  category,
  COUNT(DISTINCT key) OVER (PARTITION BY category) AS distinct_count
FROM table;

Eager to dive in? Set up your playground here.

Tools at Hand

Bridging the gap with OUTER APPLY

// Welcome to the OUTER APPLY Club! No duplicates allowed
SELECT 
  t.category, 
  dc.DistinctCount
FROM 
  (SELECT DISTINCT category FROM table) t
OUTER APPLY (
  SELECT COUNT(DISTINCT t2.key) AS DistinctCount
  FROM table t2
  WHERE t2.category = t.category
) dc;

Correlated Subqueries - Your trusty sidekick

// Subqueries, the Robin to your Batman
SELECT 
  DISTINCT category, 
  (SELECT COUNT(DISTINCT key) FROM table t2 WHERE t2.category = t1.category) AS distinct_count
FROM table t1;

Grouping with Subqueries - A timeless classic

// Subqueries and GROUP BY go together like cookies and milk
SELECT 
  category, 
  (SELECT COUNT(DISTINCT key) FROM table t2 WHERE t2.category = t1.category) AS distinct_count
FROM table t1
GROUP BY category;

Advanced Techniques

Specific Counting Conditions

// When every distinct count must pass your Criteria
SELECT 
  category, 
  COUNT(DISTINCT CASE WHEN condition THEN key ELSE NULL END) OVER (PARTITION BY category) AS conditional_distinct_count
FROM table;

Aggregation Therapy

// Injecting some aggregation aspirin to alleviate your distinct count headaches
SELECT 
  category, 
  (SELECT MIN(key) FROM table WHERE category = t.category AND condition) AS conditional_distinct_count
FROM table t
GROUP BY category;