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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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