Example: Group Count

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

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

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