Example: Subquery Using the SUM and COUNT Aggregate Functions - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

The following statement uses a nested ordered analytical function and a HAVING clause to find those items that appear in the top 1 percent of profitability in more than 20 stores:

    SELECT item, COUNT(store)
    FROM (SELECT store,item,profit,QUANTILE(100,profit) AS percentile
          FROM (SELECT ds.store, it.item, SUM(sales)-
              COUNT(sales) * it.item_cost AS profit
              FROM daily_sales AS ds, items AS it
              WHERE ds.item = it.item
              GROUP BY ds.store, it.item, it.item_cost) AS item_profit
          GROUP BY store, item, profit
          QUALIFY percentile = 0) AS top_one_percent
    GROUP BY item
    HAVING COUNT(store) >= 20;

The results of this query might look like this:

              Item  Count(Store)
     ----------------  ------------
     Chilean sea bass           99
     Mackerel                  149
     Tako                      121
     Herring                   120
     Salmon                    143