17.05 - Example: Specifying GROUP BY Using an Ordered Analytic Function - 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 specifies a GROUP BY clause with an ordered analytical function to generate report breaks where the function resets and computes a new value for the next grouping.

The example groups all items into percentile by profitability for each store and then returns only the items of interest, which, in this case, are the lowest percentile for each store.

     SELECT store, item, profit, QUANTILE(100, profit) AS percentile
     FROM (SELECT items.item, SUM(sales) -
                  (COUNT(sales)*items.item_cost) AS profit
           FROM daily_sales, items
           WHERE daily_sales.item = items.item
           GROUP BY items.item,items.itemcost) AS item_profit
     GROUP BY store, item, profit, percentile
     QUALIFY percentile = 99;

The result of this query looks like the following table:

store
-----
item
----
profit
------
percentile
----------
Eastside
Golf balls
    100.19
99
Westside
Tennis balls
   -110.00
99
Central
Bowling balls
   -986.81
99
South
Codfish balls
- 1,891.89
99
North
Bocce balls
  1,180.88
99