Example: Aggregation on Two Columns from PIVOT Results - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/djk1612415574830.ditamap
dita:ditavalPath
SQL_Functions__Expressions__and_Predicates.Upload_071421/wrg1590696035526.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
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);
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