DISTINCT with aggregates
COUNT(DISTINCT col) and similar forms aggregate only unique values.
In this page:
Syntax
SELECT COUNT(DISTINCT column), SUM(DISTINCT column)
FROM table_name;DISTINCT with aggregates
COUNT(DISTINCT city) counts unique cities, and SUM(DISTINCT x) adds each different value once.
In PostgreSQL you can also use aggregate functions like string_agg(DISTINCT name, ', ') and array_agg(DISTINCT x). DISTINCT inside aggregates is different from SELECT DISTINCT, which removes duplicate rows.
Note:
COUNT(DISTINCT x) is a common way to count unique visitors.
Example: DISTINCT with aggregates
CREATE TABLE visits (id INTEGER PRIMARY KEY, visitor TEXT, page TEXT);
INSERT INTO visits VALUES (1,'ada','home'),(2,'bob','home'),(3,'ada','pricing'),(4,'ada','home'),(5,'cy','pricing');
SELECT COUNT(*) AS hits, COUNT(DISTINCT visitor) AS unique_visitors FROM visits;
SELECT page, COUNT(*) AS hits, COUNT(DISTINCT visitor) AS unique_visitors FROM visits GROUP BY page ORDER BY page;
-- Output:
-- hits | unique_visitors
-- 5 | 3
-- page | hits | unique_visitors
-- home | 3 | 2
-- pricing | 2 | 2Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Confusing SELECT DISTINCT with COUNT(DISTINCT)
- Using DISTINCT to hide bad joins
- Forgetting DISTINCT costs a sort or hash
Chapter Summary
- COUNT(DISTINCT x) counts unique values
- SUM(DISTINCT x) adds unique values
- Works inside grouped queries
- Different from SELECT DISTINCT
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: