Example: Aggregation on Two Columns from PIVOT Results - Analytics Database - Teradata Vantage

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);

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

SELECT * FROM s1 PIVOT(SUM(SALES) FOR MON IN ('JAN', 'FEB', 'MAR')
  WITH SUM("'JAN'", "'FEB'") AS AGGR1 ) DT
order by 1;

Output:

      yr        'JAN'        'FEB'        'MAR'        AGGR1
--------  -----------  -----------  -----------  -----------
    2001          100          110          120          210
    2002          150          200          250          350
    2003          300          310            ?          610