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
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval

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