DISTINCT or GROUP BY: how to remove repetitions?

Display uniqueness or calculation by group?

SELECT DISTINCT categorie directly expresses a single list request. GROUP BY categorie becomes natural when adding COUNT, SUM or AVG.

Neither fixes a duplicate in the source table. They only shape the reading result.

The query explained

Start by defining what a row of the result should represent. This decision determines the necessary columns, groupings, and controls.

The following example isolates the main mechanism. Run it, observe the result, then change one clause at a time to understand its effect.

SELECT categorie, COUNT(*) AS produits
FROM produits
GROUP BY categorie
ORDER BY produits DESC, categorie;

Read the result like an analyst

A line now represents a category. The meter explains its weight, which a simple DISTINCT would not do.

If several columns are selected, DISTINCT applies to their complete combination; therefore precisely define the expected uniqueness.

• Write what a single line represents.
• Inspect the source keys.
• Use GROUP BY for aggregations.
• Add stable sorting.

Errors that distort the analysis

A query can be valid without correctly answering the question. The most costly errors are often silent: wrong perimeter, multiplied line or missing value interpreted as zero.

Before sharing the result, compare it to a small hand-calculated sample and keep the scope definition with the query.

• Use DISTINCT to hide a bad join.
• Believe that it only acts on the first column.
• Group by an unnecessary column and change the level of detail.
• Confuse deduplication of the result and cleaning of the source.

Get into practice

List the unique categories, then turn the query into a category count to compare the two intents.

The SQL simulator of SQL.tn only runs read queries on dummy data in your browser. You can try multiple writes and compare their results without installing any software.