17.05 - Example: Subquery Using the SUM and COUNT Aggregate Functions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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