The GROUP BY clause organizes the rows returned from a SELECT statement into groups. Within each group, you can apply an aggregate function, like count() or sum() to get the number of items or sum, respectively.
In this example, the SELECT query counts the number of rows for each category and groups the counts by category.
SELECT Category, COUNT(*) AS "cnt"
FROM "Superstore"
GROUP BY Category;
| Category | cnt |
|---|---|
| Furniture | 2,121 |
| Office Supplies | 6,026 |
| Technology | 1,847 |
You can also group by multiple columns. This query generates the group of cities that contain stores in each category. The count column shows how many stores there are in each city.
SELECT Category, City, count(*) as cnt
FROM "Superstore"
GROUP BY Category, City
Here are the first ten results.
| Category | City | cnt |
|---|---|---|
| Furniture | Akron | 2 |
| Alexandria | 3 | |
| Allen | 1 | |
| Allentown | 1 | |
| Amarillo | 4 | |
| Anaheim | 8 | |
| Andover | 1 | |
| Apopka | 1 | |
| Apple Valley | 2 |
note: Grouping by ordinal number is not supported. You can't refer to a results column or an expression created by input values by its index value. To shorten a lengthy expression in your query, use an alias. For example, this query throws an error.
SELECT Category, City, count(*) as cnt
FROM "Superstore"
GROUP BY 1, 2