GROUP BY ROLLUP

ROLLUP is a sub-clause of GROUP BY that creates and displays aggregations of column data. The results output of ROLLUP is based on column order in your query.

ROLLUP supports the following aggregate functions.

• Average
• Count
• Min
• Max
• Sum

This example first groups the results by category and sub-category, and runs Sum(Profit), an aggregate function on each resulting row. By modifying theGROUP BY clause with ROLLUP, the query "rolls up" the results into subtotals and grand totals.

SELECT Category, Sub_Category, Sum(Profit) as TotalProfit
FROM "Superstore"
GROUP BY ROLLUP(Category, Sub_Category);

Notice how GROUP BY ROLLUP (Category, Sub_Category) creates groups for each column combination:

Category, Sub-Category
Category
() — null

The query first groups the total profit for each subcategory of a given category. Next, it groups is the total profit for a single category. Once each category's total profit is accounted for, the query generates the total profit for all categories.

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.