Microsoft SQL Server GROUP BY Simple Grouping
Example
Orders Table
| CustomerId | ProductId | Quantity | Price |
|---|---|---|---|
| 1 | 2 | 5 | 100 |
| 1 | 3 | 2 | 200 |
| 1 | 4 | 1 | 500 |
| 2 | 1 | 4 | 50 |
| 3 | 5 | 6 | 700 |
When grouping by a specific column, only unique values of this column are returned.
SELECT customerId
FROM orders
GROUP BY customerId;
Return value:
| customerId |
|---|
| 1 |
| 2 |
| 3 |
Aggregate functions like count() apply to each group and not to the complete table:
SELECT customerId,
COUNT(productId) as numberOfProducts,
sum(price) as totalPrice
FROM orders
GROUP BY customerId;
Return value:
| customerId | numberOfProducts | totalPrice |
|---|---|---|
| 1 | 3 | 800 |
| 2 | 1 | 50 |
| 3 | 1 | 700 |
