topic Re: Group By one column in Teradata in SAS Studio

Group By one column in Teradata

crawfe — Thu, 30 Jan 2020 13:23:09 GMT

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.

Re: Group By one column in Teradata

Tom — Thu, 30 Jan 2020 18:19:24 GMT

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

Re: Group By one column in Teradata

crawfe — Fri, 31 Jan 2020 15:39:56 GMT

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.