Using DISTINCT with Aggregate Functions0%

Using DISTINCT with Aggregate Functions

Beginner12 min read•Updated: 2026-09-12

Using DISTINCT with Aggregate Functions: Deduplication Rules

When calculating business metrics, you often need to differentiate between the raw volume of events and the number of unique entities involved. For example, a customer may place 10 orders in a single week. If you calculate total orders, you want to count all 10. But if you want to know how many distinct customers placed orders, you must deduplicate before counting. In SQL, this is achieved by embedding DISTINCT inside aggregate functions.

1. Syntax of DISTINCT Inside Aggregates

1AGGREGATE_FUNCTION(DISTINCT column_name)

2. `COUNT(*)` vs `COUNT(col)` vs `COUNT(DISTINCT col)`

 Table: web_visits
 +----------+-------------+---------+
 | visit_id | visitor_ip  | user_id |
 +----------+-------------+---------+
 |        1 | 192.168.1.1 |     101 |
 |        2 | 192.168.1.1 |     101 |
 |        3 | 192.168.1.5 |    NULL |
 |        4 | 192.168.1.9 |     102 |
 |        5 | 192.168.1.1 |     101 |
 +----------+-------------+---------+

Let us observe how each query evaluates against the table above:

1-- 1. Total Page Hits (All rows):
2SELECT COUNT(*) FROM web_visits;
3-- Result: 5
4 
5-- 2. Identified Logged-in Hits (Non-null user_id):
6SELECT COUNT(user_id) FROM web_visits;
7-- Result: 4 (Skips the NULL row)
8 
9-- 3. Unique Logged-in Users:
10SELECT COUNT(DISTINCT user_id) FROM web_visits;
11-- Result: 2 (User 101 and User 102)
12 
13-- 4. Unique IP Addresses:
14SELECT COUNT(DISTINCT visitor_ip) FROM web_visits;
15-- Result: 3 ('192.168.1.1', '192.168.1.5', '192.168.1.9')

3. Combining Multiple DISTINCT Aggregates in One Query

MySQL allows executing multiple distinct aggregate calculations simultaneously:

1SELECT 
2    department,
3    COUNT(*) AS total_employees,
4    COUNT(DISTINCT job_title) AS distinct_roles_count,
5    COUNT(DISTINCT manager_id) AS distinct_managers_count,
6    AVG(salary) AS dept_avg_salary
7FROM employees
8GROUP BY department;

4. Can You Use `DISTINCT` with `SUM()` or `AVG()`?

Yes, although it is less common:

SUM(DISTINCT salary): Sums each unique salary value only once. If three engineers earn ₹80,000, it only includes ₹80,000 once in the sum.
AVG(DISTINCT salary): Calculates the average of unique salary values, ignoring frequency.

1-- Notice: Rarely used in standard payroll, but useful in deduplicating multi-join fan-outs!
2SELECT SUM(DISTINCT unit_price) FROM order_details;

5. Best Practices & Common Pitfalls

High Memory Overhead of COUNT(DISTINCT): Calculating COUNT(DISTINCT col) requires MySQL to maintain a temporary hash set or B-Tree in memory to track all previously seen values. Across 100 million rows with high cardinality, this can consume significant server memory.
COUNT(DISTINCT col1, col2): MySQL uniquely supports counting distinct combinations of multiple columns:

1  -- Count unique combinations of city and state:
2  SELECT COUNT(DISTINCT city, state) FROM locations;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In a table with 5 rows where a column contains values [10, 10, 20, 20, NULL], what does COUNT(DISTINCT column) return?

Question 2

What is the fundamental difference between COUNT(*) and COUNT(DISTINCT user_id)?

Question 3

Does MySQL permit counting distinct combinations across multiple columns in a single function call (e.g., COUNT(DISTINCT col1, col2))?

Question 4

What will SUM(DISTINCT val) return for the dataset [100, 100, 200, 300]?

Question 5

Why can executing COUNT(DISTINCT) on a column with 50 million unique values be slow?

Next Lesson

Database Backups with mysqldump

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Filtering Groups with HAVING vs WHERE	Database Backups with mysqldump

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Database Backups with mysqldump

Using DISTINCT with Aggregate Functions

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Using DISTINCT with Aggregate Functions: Deduplication Rules

1. Syntax of DISTINCT Inside Aggregates

1AGGREGATE_FUNCTION(DISTINCT column_name)

2. `COUNT(*)` vs `COUNT(col)` vs `COUNT(DISTINCT col)`

 Table: web_visits
 +----------+-------------+---------+
 | visit_id | visitor_ip  | user_id |
 +----------+-------------+---------+
 |        1 | 192.168.1.1 |     101 |
 |        2 | 192.168.1.1 |     101 |
 |        3 | 192.168.1.5 |    NULL |
 |        4 | 192.168.1.9 |     102 |
 |        5 | 192.168.1.1 |     101 |
 +----------+-------------+---------+

Let us observe how each query evaluates against the table above:

1-- 1. Total Page Hits (All rows):
2SELECT COUNT(*) FROM web_visits;
3-- Result: 5
4 
5-- 2. Identified Logged-in Hits (Non-null user_id):
6SELECT COUNT(user_id) FROM web_visits;
7-- Result: 4 (Skips the NULL row)
8 
9-- 3. Unique Logged-in Users:
10SELECT COUNT(DISTINCT user_id) FROM web_visits;
11-- Result: 2 (User 101 and User 102)
12 
13-- 4. Unique IP Addresses:
14SELECT COUNT(DISTINCT visitor_ip) FROM web_visits;
15-- Result: 3 ('192.168.1.1', '192.168.1.5', '192.168.1.9')

3. Combining Multiple DISTINCT Aggregates in One Query

MySQL allows executing multiple distinct aggregate calculations simultaneously:

1SELECT 
2    department,
3    COUNT(*) AS total_employees,
4    COUNT(DISTINCT job_title) AS distinct_roles_count,
5    COUNT(DISTINCT manager_id) AS distinct_managers_count,
6    AVG(salary) AS dept_avg_salary
7FROM employees
8GROUP BY department;

4. Can You Use `DISTINCT` with `SUM()` or `AVG()`?

Yes, although it is less common:

SUM(DISTINCT salary): Sums each unique salary value only once. If three engineers earn ₹80,000, it only includes ₹80,000 once in the sum.
AVG(DISTINCT salary): Calculates the average of unique salary values, ignoring frequency.

1-- Notice: Rarely used in standard payroll, but useful in deduplicating multi-join fan-outs!
2SELECT SUM(DISTINCT unit_price) FROM order_details;

5. Best Practices & Common Pitfalls

High Memory Overhead of COUNT(DISTINCT): Calculating COUNT(DISTINCT col) requires MySQL to maintain a temporary hash set or B-Tree in memory to track all previously seen values. Across 100 million rows with high cardinality, this can consume significant server memory.
COUNT(DISTINCT col1, col2): MySQL uniquely supports counting distinct combinations of multiple columns:

1  -- Count unique combinations of city and state:
2  SELECT COUNT(DISTINCT city, state) FROM locations;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In a table with 5 rows where a column contains values [10, 10, 20, 20, NULL], what does COUNT(DISTINCT column) return?

Question 2

What is the fundamental difference between COUNT(*) and COUNT(DISTINCT user_id)?

Question 3

Does MySQL permit counting distinct combinations across multiple columns in a single function call (e.g., COUNT(DISTINCT col1, col2))?

Question 4

What will SUM(DISTINCT val) return for the dataset [100, 100, 200, 300]?

Question 5

Why can executing COUNT(DISTINCT) on a column with 50 million unique values be slow?

Next Lesson

Database Backups with mysqldump

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Filtering Groups with HAVING vs WHERE	Database Backups with mysqldump

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Using DISTINCT with Aggregate Functions: Deduplication Rules

1. Syntax of DISTINCT Inside Aggregates

2. COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)

3. Combining Multiple DISTINCT Aggregates in One Query

4. Can You Use DISTINCT with SUM() or AVG()?

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Database Backups with mysqldump

Related Lessons

Practice Quiz

Loading Page Content...

Using DISTINCT with Aggregate Functions: Deduplication Rules

1. Syntax of DISTINCT Inside Aggregates

2. COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)

3. Combining Multiple DISTINCT Aggregates in One Query

4. Can You Use DISTINCT with SUM() or AVG()?

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Database Backups with mysqldump

Related Lessons

Practice Quiz

2. `COUNT(*)` vs `COUNT(col)` vs `COUNT(DISTINCT col)`

4. Can You Use `DISTINCT` with `SUM()` or `AVG()`?

2. `COUNT(*)` vs `COUNT(col)` vs `COUNT(DISTINCT col)`

4. Can You Use `DISTINCT` with `SUM()` or `AVG()`?