Collapses rows sharing the same values of chosen expressions into one row per bucket, with aggregate functions evaluated for each bucket. Writing GROUP BY ALL picks every expression in the select list that isn't an aggregate.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains GROUP BY in context, with comparison tables and the common traps.
Terms in this definition
- VALUES
Returns in DAX the distinct column values, or table rows, still visible after filters are applied, sometimes with an extra blank entry. CALCULATE often takes the result as a table filter.
- ALL
A DAX function that ignores any filters and gives back every row of a table or every value of the named columns. Used within CALCULATE, it works as a modifier that clears filters, although REMOVEFILTERS states that intent more clearly where it is available.
- SELECT
A DML command in SQL used to retrieve rows from one table or several.
- List
Permission on Key Vault secrets allowing a caller to enumerate those in a vault, though their values are not returned.
Related terms
- CUBE
A Spark SQL
GROUP BYextension that returns totals for each possible combination of the named columns, which is 2^n grouping sets once the overall total is counted.ROLLUP, by contrast, returns only the hierarchy-based subset. - GROUPING SETS
Lets one
GROUP BYquery return results for several different groupings at once, giving the same output as stitching individual aggregations together withUNION ALL.CUBEandROLLUPare convenient shorthands built on it. - HAVING
Applies a condition to groups produced by GROUP BY, often on an aggregate such as SUM(). WHERE works earlier, on individual rows before they are grouped.
- ROLLUP
An option for GROUP BY, available in both Spark SQL and T-SQL, that produces subtotals at each level of a hierarchy plus an overall total; for example ROLLUP(a, b) groups by (a, b), then by (a), then by nothing. CUBE goes further and covers every combination of the columns.
- SUMMARIZE
Groups a table in DAX, giving a row for every combination of the columns chosen to group by, and can add named columns that hold aggregations.
- Window function
A function evaluated OVER a window defined by PARTITION BY, ORDER BY and optionally a frame; unlike GROUP BY aggregation it doesn't collapse rows, but adds to each one a value worked out from its related rows. Examples are RANK, ROW_NUMBER, LAG and running totals.
- Window functions
Calculate across rows related to whichever row is current, e.g. rankings or running totals. DAX provides OFFSET, WINDOW, INDEX, RANK and ROWNUMBER, partitioned and ordered through PARTITIONBY and ORDERBY; T-SQL uses OVER, applied once GROUP BY and HAVING finish.