Trimming results

MySQL allows for two different ways of trimming results when using aggregate functions. One is a keyword that removes redundant data. The other is a function that pools the data into a single string value.

DISTINCT

The purpose of DISTINCT is to ensure that all results are unique. The examples above do not discriminate between duplicate values. Each value is treated as a separate record without quantitative comparison to the others, thus allowing for redundancy in the results.

This redundancy works well when comprehensiveness is required but can otherwise skew the results of a query. For example, if we want to know how many unique ratings are used in the film table of sakila, we would not want to use this query:

SELECT COUNT(rating) FROM film;

The results show every record that has a value for the rating column.

+---------------+
| COUNT(rating) |
+---------------+
|          1000 |
+---------------+

Instead of retrieving the actual values and process 1000 records in our program...