Multiple Column Summary in Teradata

Question

First question here, hope you can help.

I have a table with 75+ columns, first is an ID, the rest are just 1 or null flags

Example:

UNIQUE_IDENT, SERVICE_FLAG, P1_PREFA, P2_PREFB, etc etc >> P75_PREF75
987651651,    1,            1,        1,                   1

I want to sum each column without writing a sum statement for all 75 columns, I've seen it done in SAS as a Proc Summary

Service_Flag =1 for all users in this instance

proc summary data= TranzSeg_Permission ;
class SERVICE_FLAG;
var _:;
output out=segments_rollup (where=(_TYPE_=1) drop= _FREQ_) sum=;
run;

There must be a simple way?

Thanks, really helpful... – Chris Buck Oct 07 '15 at 11:30 — Chris Buck, Oct 07 '15 at 11:30

score 0 · Answer 1 · answered Oct 05 '15 at 11:32

You can do this using conditional aggregation, but you have to list each column:

select unique_ident,
       min(service_flag) as service_flag,
       min(p1_prefa) as p1_prefa,
       . . .
from TranzSeg_Permission
group by unique_ident;

I am using min() because you say that "all" rows with the ident have service flag = 1.

You can generate the code by getting the column names and using either a SQL statement or spreadsheet.

Multiple Column Summary in Teradata

1 Answers1