SQL SQL Group By vs Distinct Difference between GROUP BY and DISTINCT
Example
GROUP BY is used in combination with aggregation functions. Consider the following table:
| orderId | userId | storeName | orderValue | orderDate |
|---|---|---|---|---|
| 1 | 43 | Store A | 25 | 20-03-2016 |
| 2 | 57 | Store B | 50 | 22-03-2016 |
| 3 | 43 | Store A | 30 | 25-03-2016 |
| 4 | 82 | Store C | 10 | 26-03-2016 |
| 5 | 21 | Store A | 45 | 29-03-2016 |
The query below uses GROUP BY to perform aggregated calculations.
SELECT
storeName,
COUNT(*) AS total_nr_orders,
COUNT(DISTINCT userId) AS nr_unique_customers,
AVG(orderValue) AS average_order_value,
MIN(orderDate) AS first_order,
MAX(orderDate) AS lastOrder
FROM
orders
GROUP BY
storeName;
and will return the following information
| storeName | total_nr_orders | nr_unique_customers | average_order_value | first_order | lastOrder |
|---|---|---|---|---|---|
| Store A | 3 | 2 | 33.3 | 20-03-2016 | 29-03-2016 |
| Store B | 1 | 1 | 50 | 22-03-2016 | 22-03-2016 |
| Store C | 1 | 1 | 10 | 26-03-2016 | 26-03-2016 |
While DISTINCT is used to list a unique combination of distinct values for the specified columns.
SELECT DISTINCT
storeName,
userId
FROM
orders;
| storeName | userId |
|---|---|
| Store A | 43 |
| Store B | 57 |
| Store C | 82 |
| Store A | 21 |
