Ordered Analytical Functions Window Examples | Teradata Vantage - 17.10 - Example: Group Count - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

The following SQL query might yield the results that follow it, where the group count for sales is returned for each of the four partitions defined by city and kind. Notice that rows that have no sales are not counted.

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