GROUP BY

A GROUP BY clause is part of a SelectExpression, that groups a result into subsets that have matching values for one or more columns. In each group, no two rows have the same value for the grouping column or columns. NULLs are considered equivalent for grouping purposes.

You typically use a GROUP BY clause in conjunction with an aggregate expression.

Using the ROLLUP syntax, you can specify that multiple levels of grouping should be computed at once.

Syntax

column-Name-or-Position

Must be either the name or position of a column from the current scope of the query; there can be no columns from a query block outside the current scope. For example, if a GROUP BY clause is in a subquery, it cannot refer to columns in the outer query.

Usage Notes

SelectItems in the SelectExpression with a GROUP BY clause must contain only aggregates or grouping columns.

Examples

Create our Test Table:

CREATE TABLE Test1
(
    TRACK_SEQ VARCHAR(40),
    TRACK_CD VARCHAR(18),
    REC_SEQ_NBR BIGINT,
    INDIV_ID BIGINT,
    BIZ_ID BIGINT,
    ADDR_ID BIGINT,
    HH_ID BIGINT,
    TRIAD_CB_DT DATE
);

Populate our Test Table:

CREATE TABLE Test1
INSERT INTO Test1 VALUES
    ('1','A',1,1,1,1,1,'2017-07-01'),
    ('1','A',1,1,2,2,2,'2017-07-02'),
    ('3','C',3,1,3,3,3,'2017-07-03'),
    ('1','A',1,2,1,1,1,'2017-07-01'),
    ('1','A',1,2,2,2,2,'2017-07-02'),
    ('3','C',3,2,3,3,3,'2017-07-03');

Example: Query Using Column Names:

SELECT indiv_id, track_seq, rec_seq_nbr, triad_cb_dt, ROW_NUMBER()
OVER (PARTITION BY indiv_id ORDER BY triad_cb_dt desc,rec_seq_nbr desc) AS ranking
FROM Test1
GROUP BY indiv_id,track_seq,rec_seq_nbr,triad_cb_dt;
INDIV_ID |TRACK_SEQ |REC_SEQ_NBR |TRIAD_CB_&|RANKING
---------------------------------------------------------
1        |1         |1           |2017-07-01|3
1        |1         |1           |2017-07-02|2
1        |3         |3           |2017-07-03|1
2        |1         |1           |2017-07-01|3
2        |1         |1           |2017-07-02|2
2        |3         |3           |2017-07-03|1
6 rows selected

Example: Query Using Column Positions:

SELECT indiv_id, track_seq, rec_seq_nbr, triad_cb_dt, ROW_NUMBER()
OVER (PARTITION BY indiv_id ORDER BY triad_cb_dt desc,rec_seq_nbr desc) AS ranking
FROM Test1
GROUP BY 1,2,3,4;
INDIV_ID |TRACK_SEQ |REC_SEQ_NBR |TRIAD_CB_&|RANKING
---------------------------------------------------------
1        |1         |1           |2017-07-01|3
1        |1         |1           |2017-07-02|2
1        |3         |3           |2017-07-03|1
2        |1         |1           |2017-07-01|3
2        |1         |1           |2017-07-02|2
2        |3         |3           |2017-07-03|1
6 rows selected

See Also