Use the COUNT aggregate analytical function to show how many rows are included in each window



SQL> create table TestTable (
  2    x    number primary key,
  3    y   number
  4  );

Table created.

SQL> insert into TestTable values (1, 7 );

1 row created.

SQL> insert into TestTable values (2, 1 );

1 row created.

SQL> insert into TestTable values (3, 2 );

1 row created.

SQL> insert into TestTable values (4, 5 );

1 row created.

SQL> insert into TestTable values (5, 7 );

1 row created.

SQL> insert into TestTable values (6, 34 );

1 row created.

SQL> insert into TestTable values (7, 32 );

1 row created.

SQL> insert into TestTable values (8, 43 );

1 row created.

SQL> insert into TestTable values (9, 87 );

1 row created.

SQL> insert into TestTable values (10, 32 );

1 row created.

SQL> insert into TestTable values (11, 12 );

1 row created.

SQL> insert into TestTable values (12, 16 );

1 row created.

SQL> insert into TestTable values (13, 63 );

1 row created.

SQL> insert into TestTable values (14, 74 );

1 row created.

SQL> insert into TestTable values (15, 36 );

1 row created.

SQL> insert into TestTable values (16, 56 );

1 row created.

SQL> insert into TestTable values (17, 2 );

1 row created.

SQL>
SQL> select * from TestTable;

         X          Y
---------- ----------
         1          7
         2          1
         3          2
         4          5
         5          7
         6         34
         7         32
         8         43
         9         87
        10         32
        11         12
        12         16
        13         63
        14         74
        15         36
        16         56
        17          2

17 rows selected.

SQL>
SQL> -- Use the COUNT aggregate analytical function to show how many rows are included in each window:
SQL>
SQL> SELECT x, y,
  2    COUNT(y) OVER(ORDER BY x
  3    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) Howmanyrows
  4  FROM TestTable
  5  ORDER BY x;

         X          Y HOWMANYROWS
---------- ---------- -----------
         1          7           2
         2          1           3
         3          2           3
         4          5           3
         5          7           3
         6         34           3
         7         32           3
         8         43           3
         9         87           3
        10         32           3
        11         12           3
        12         16           3
        13         63           3
        14         74           3
        15         36           3
        16         56           3
        17          2           2

17 rows selected.

SQL>
SQL>
SQL> drop table TestTable;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	count(*) over partition, order by, range unbounded preceding
2.	count(*) over partition by, order by, range unbounded preceding
3.	Employee salary report with avg salary for the previous 12 months
4.	avg over range between
5.	Is our average total_order_price increasing or decreasing?
6.	analytic order-by clause
7.	avg over and avg over order by
8.	Sum over order by
9.	Sum over partition by and order by
10.	avg over order by range
11.	average 5 before, after
12.	Row-ordering is done first and then the moving average
13.	Avg over ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
14.	SUM(y) OVER(ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
15.	To see how the moving average window can expand
16.	Uses dates and logical offset of seven days preceding
17.	A seven-day MAX and MIN on Tuesdays
18.	A seven-day MAX and MIN on Tuesdays: using TO_CHAR function
19.	Displaying a Running Total Using SUM as an Analytical Function
20.	Reporting on a Sum

Use the COUNT aggregate analytical function to show how many rows are included in each window : Aggregrate Analytical « Analytical Functions « Oracle PL / SQL

Related examples in the same category