Window Function COUNT

This article is an extension of the window function, to follow up on each of the window functions.

The COUNT function will counts each row that contains non-NULL values for the given expression.

An example usage over the test data set would be:

SELECT sale_date, salesperson,
	   COUNT(*) OVER (
         PARTITION BY sale_date
         ORDER BY sale_id
         ROWS UNBOUNDED PRECEDING
       ) as sale_number
FROM public.sales
ORDER BY sale_date;

We list the sale_date and salesperson out for every sale on record. The COUNT partitions by the date of the sale made. This makes the sale_number tell us what number the sale was for that given date.

Taking 2018-05-27, we can see that John got the first sale, June got the second and third.