To count all the rows, including rows that have no sales, use COUNT(*). Here is an example that counts the number of rows remaining in the partition after the current row:
SELECT city, kind, sales, profit, COUNT(*) OVER (PARTITION BY city, kind ORDER BY profit DESC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM activity_month;
Output:
city kind sales profit Remaining Count(*) ------- -------- ----- ------ ------------------ LA Canvas 20 120 ? LA Canvas 125 190 1 LA Canvas 45 320 2 LA Canvas 125 400 3 LA Leather ? ? ? LA Leather 20 40 1 Seattle Canvas 15 30 ? Seattle Canvas 20 30 1 Seattle Canvas 20 100 2 Seattle Leather ? ? ? Seattle Leather 35 50 1
The sort order that you specify in the window specification defines the sort order of the rows over which the function is applied, not the ordering of the results.
In the example, the DESC sort order is specified for the computation, but the results are returned in the reverse order.
To order the results, use the ORDER BY phrase in the SELECT statement:
SELECT city, kind, sales, profit, COUNT(*) OVER (PARTITION BY city, kind ORDER BY profit DESC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM activity_month ORDER BY city, kind, profit DESC;
Output:
city kind sales profit Remaining Count(*) ------- -------- ----- ------ ------------------ LA Canvas 125 400 3 LA Canvas 45 320 2 LA Canvas 125 190 1 LA Canvas 20 120 ? LA Leather 20 40 1 LA Leather ? ? ? Seattle Canvas 20 100 2 Seattle Canvas 20 30 1 Seattle Canvas 15 30 ? Seattle Leather 35 50 1 Seattle Leather ? ? ?