Example: Aggregation on Two Columns from PIVOT Results

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

This example shows how to sum sales in the months of Jan and Feb for each year. This is an aggregation on two columns from the PIVOT result.

Table s1 is defined as:
CREATE TABLE s1 (yr INTEGER, mon VARCHAR(20), sales INTEGER);
The table contains:
SELECT * FROM s1;
yr      mon      sales
-----   ---      -----
2001    Jan       100
2003    Jan       300
2002    Jan       150
2001    Feb       110
2003    Feb       310
2002    Feb       200
2001    Mar       120
2002    Mar       250
The PIVOT query is:
SELECT * FROM s1 PIVOT(SUM(SALES) FOR MON IN ('JAN', 'FEB', 'MAR')
  WITH SUM("'JAN'", "'FEB'") AS AGGR1 ) DT
order by 1;
AGGR1 is the name of the aggregated result column.
Output:
      yr        'JAN'        'FEB'        'MAR'        AGGR1
--------  -----------  -----------  -----------  -----------
    2001          100          110          120          210
    2002          150          200          250          350
    2003          300          310            ?          610