Group By one column in Teradata

[crawfe]

Hi. I am learning to work with teradata code in DI Studio to speed up processing of large tables

(Advice from my admin).

This might be more of a strategy question. How to SUM by a Group A only.

The Group By requires all the non-aggregated columns to be listed, which seems awkward.

Please correct me if I understand it wrong.

HAVE: Columns A B C D(variable)

WANT:   The SUM of D(variable) Grouped By A only.

If 3 different A values:

3 groups: Group A1 - sum(Dvar), Group A2 - sum(Dvar), Group A3 - sum(Dvar)

HOW (in TD): SUM(D(variable); Group By A B C (must include all columns?!)

GET?:

3 x 3 combinations of grouping: A1B1C1 sum(Dvar), A1B1C2 sum(Dvar), A1B1C3 sum(Dvar)etc.

I could do brute force and split off a table of A D(variable), perform my sum/group, then rejoin.

Is there a more efficient way?

Any advice would be appreciated! Eventually this will be reported in Viya.

[Tom]

It is is not at all clear what you are asking for.  Are you asking to generate output like you would get with PROC SUMMARY where you have some rows that represents values grouped say just variable A and others where the data is grouped by A and B. Or A, B and C.

If so then you probably are looking for the CUBE GROUPING SETS feature of Teradata.

https://docs.teradata.com/reader/b8dd8xEYJnxfsq4uFRrHQQ/5uCbXZ6cjrhyOZfoCwkiDg

[crawfe · Accepted solution]

Yes, I want a column of var by GroupA only, Group AB only, Group ABC only. Teradata seems a little harder to do that (in DI Studio). As you say, the answer is probably in the Group By Rollup variations. Thanks.