16.20 - Example: Remaining Count - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

To count all the rows, including rows that have no sales, use COUNT(*). Here is an example that counts the number of rows remaining in the partition after the current row:

   SELECT city, kind, sales, profit,
   COUNT(*) OVER (PARTITION BY city, kind ORDER BY profit DESC
                  ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
   FROM activity_month;
   
   city     kind      sales  profit  Remaining Count(*)
   -------  --------  -----  ------  ------------------
   LA       Canvas       20     120                   ?
   LA       Canvas      125     190                   1
   LA       Canvas       45     320                   2
   LA       Canvas      125     400                   3
   LA       Leather       ?       ?                   ?
   LA       Leather      20      40                   1
   Seattle  Canvas       15      30                   ?
   Seattle  Canvas       20      30                   1
   Seattle  Canvas       20     100                   2
   Seattle  Leather       ?       ?                   ?
   Seattle  Leather      35      50                   1

Note that the sort order that you specify in the window specification defines the sort order of the rows over which the function is applied; it does not define the ordering of the results.

In the example, the DESC sort order is specified for the computation, but the results are returned in the reverse order.

To order the results, use the ORDER BY phrase in the SELECT statement:

   SELECT city, kind, sales, profit,
   COUNT(*) OVER (PARTITION BY city, kind ORDER BY profit DESC
                  ROWS BETWEEN 1 FOLLOWING AND 
                  UNBOUNDED FOLLOWING)
   FROM activity_month
   ORDER BY city, kind, profit DESC;
   
   city     kind      sales  profit  Remaining Count(*)
   -------  --------  -----  ------  ------------------
   LA       Canvas      125     400                   3
   LA       Canvas       45     320                   2
   LA       Canvas      125     190                   1
   LA       Canvas       20     120                   ?
   LA       Leather      20      40                   1
   LA       Leather       ?       ?                   ?
   Seattle  Canvas       20     100                   2
   Seattle  Canvas       20      30                   1
   Seattle  Canvas       15      30                   ?
   Seattle  Leather      35      50                   1
   Seattle  Leather       ?       ?                   ?