Example - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantageā„¢

The following example shows how the SQL query to calculate a frequency of gender to marital status would appear using Teradata Warehouse Miner.

SELECT gender, marital_status, xcnt,xpct
   ,CSUM(xcnt, xcnt DESC, gender, marital_status) AS xcum_cnt
   ,CSUM(xpct, xcnt DESC, gender, marital_status) AS xcum_pct
   ,RANK(xcnt DESC, gender ASC, marital_status ASC) AS xrank
FROM  
   (SELECT gender, marital_status, COUNT(*) AS xcnt
      ,100.000 * xcnt / xall (FORMAT 'ZZ9.99') AS xpct
   FROM customer_table A,
      (SELECT COUNT(*) AS xall
      FROM customer_table) B
GROUP BY gender, marital_status, xall
HAVING xpct >= 1) T1
QUALIFY xrank <= 8
ORDER BY xcnt DESC, gender, marital_status

The result for this query looks like the following table.

gender marital_status xcnt xpct xcum_cnt xcum_pct xrank
F Married 3910093 36.71 3910093 36.71 1
M Married 2419511 22.71 6329604 59.42 2
F Divorced 1612130 15.13 7941734 74.55 3
M Divorced 1412624 3.26 9354358 87.81 4
F Single 491224 4.61 9845582 92.42 5
F Widowed 319881 3.01 10165463 95.43 6
M Single 319794 3.00 10485257 98.43 7
M Widowed 197131 1.57 10652388 100.00 8