​
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
  [ROWS | 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
  • ROWS | 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)

​
Time Series: COUNT(*) with RANGE for Last 90 Days

In this example, we will demonstrate counting the number of sales within a 90-day window prior to each sale, based on dateid:

SELECT salesid, dateid, qty,
  COUNT(*) OVER (
    ORDER BY dateid
    RANGE BETWEEN INTERVAL '90 days' PRECEDING AND CURRENT ROW
  ) AS sales_count_90d
FROM winsales
ORDER BY dateid;

This query above counts the number of sales transactions within a 90-day window before each dateid, including the current sale:

 salesid |   dateid   | qty | sales_count_90d 
---------+------------+-----+-----------------
   30001 | 2003-08-02 |  10 |               1
   10001 | 2003-12-24 |  10 |               2
   10005 | 2003-12-24 |  30 |               2
   40001 | 2004-01-09 |  40 |               3
   10006 | 2004-01-18 |  10 |               4
   20001 | 2004-02-12 |  20 |               6
   40005 | 2004-02-12 |  10 |               6
   20002 | 2004-02-16 |  20 |               7
   30003 | 2004-04-18 |  15 |               6
   30004 | 2004-04-18 |  20 |               6
   30007 | 2004-09-07 |  30 |               1