Running Count

In SQL, a window function allows us to perform aggregate functions on a set of query rows. While an aggregate function groups query rows as a single row result, a window function produces a result for each query row.

In this article, we will demonstrate how we can retrieve a running count of items within a class of product.

We will be using the classicmodels database, which you can download from mysqltutorial for this article.

Running Count, by Product Line

Our goal is to come up with a running count, for each unique product line. For brevity, we will be using a subset of the products table.

SELECT
   productName,
   productLine 
FROM
   products 
WHERE
   productLine IN 
   (
      'Trains',
      'Planes'
   )
ORDER BY
   productLine ASC;

The ROW_NUMBER() function allows us to generate a running count. Do note that it must be paired with the OVER(PARTITION BY ...) clause.

SELECT
   productName,
   productLine,
   ROW_NUMBER() OVER(PARTITION BY productLine) AS rowNumber 
FROM
   products 
WHERE
   productLine IN 
   (
      'Trains',
      'Planes'
   )
ORDER BY
   productLine ASC,
   rowNumber ASC;