​
Overview

The COUNT() window function allows you to retrieve the number of records that meet a specific criteria. When using it with he RANGE clause, it allows you to perform counts within a defined range based on the values of the current row. This function can be used with all data types supported by Oxla.

​
Syntax

There are two available variants of that function:

  • COUNT(*): counts all rows in the target table, regardless of whether they contain NULL values or not
  • COUNT(expression): counts the number of non-NULL values in a specific column or expression

The syntax for this function is as follows:

COUNT RANGE
COUNT(expression) OVER (
  [PARTITION BY partition_expression]
  ORDER BY sort_expression
  RANGE BETWEEN start_value AND end_value
)

The COUNT() window function always return BIGINT as an output, which represents the total number of rows in a table irrespective of the input types.

​
Parameters

  • expression: input’s column or expression that the function operates on
  • PARTITION BY: optional clause, which divides the result set into partitions to which the function is applied
  • RANGE BETWEEN: range-based window frame relative to the current row

​
Examples

For the needs of this section, we will create a winsales table that stores the details of some sales transactions:

CREATE TABLE winsales(
    salesid int,
    dateid date,
    sellerid int,
    buyerid text,
    qty int,
    qty_shipped int);
INSERT INTO winsales VALUES
    (30001, '8/2/2003', 3, 'b', 10, 10),
    (10001, '12/24/2003', 1, 'c', 10, 10),
    (10005, '12/24/2003', 1, 'a', 30, null),
    (40001, '1/9/2004', 4, 'a', 40, null),
    (10006, '1/18/2004', 1, 'c', 10, null),
    (20001, '2/12/2004', 2, 'b', 20, 20),
    (40005, '2/12/2004', 4, 'a', 10, 10),
    (20002, '2/16/2004', 2, 'c', 20, 20),
    (30003, '4/18/2004', 3, 'b', 15, null),
    (30004, '4/18/2004', 3, 'b', 20, null),
    (30007, '9/7/2004', 3, 'c', 30, null);

​
COUNT(*)

In this example, we will focus on executing the variant of this function that counts all rows in the target table:

SELECT salesid, qty
  COUNT(*) OVER (ORDER BY salesid rows unbounded preceding) AS count
FROM winsales
ORDER BY salesid;

The output of the code abote displays the sales ID, quantity and the count of all rows from the start of the data window:

 salesid | qty | count 
---------+-----+-------
   10001 |  10 |     1
   10005 |  30 |     2
   10006 |  10 |     3
   20001 |  20 |     4
   20002 |  20 |     5
   30001 |  10 |     6
   30003 |  15 |     7
   30004 |  20 |     8
   30007 |  30 |     9
   40001 |  40 |    10
   40005 |  10 |    11
(11 rows)

​
COUNT(expression)

In this example, we will focus on executing the variant of this function that counts the number of non-NULL values in a specific expression:

SELECT salesid, qty, qty_shipped,
  COUNT(qty_shipped) OVER (ORDER BY salesid rows unbounded preceding) AS count
FROM winsales
ORDER BY salesid;

Here is the output for the query presented above:

 salesid | qty | qty_shipped | count 
---------+-----+-------------+-------
   10001 |  10 |          10 |     1
   10005 |  30 |             |     1
   10006 |  10 |             |     1
   20001 |  20 |          20 |     2
   20002 |  20 |          20 |     3
   30001 |  10 |          10 |     4
   30003 |  15 |             |     4
   30004 |  20 |             |     4
   30007 |  30 |             |     4
   40001 |  40 |             |     4
   40005 |  10 |          10 |     5
(11 rows)