Utilizing FIRST_VALUE to calculate running totals and cumulative sums in SQL

26 Oct 2023

When working with SQL, there are often cases where we need to calculate running totals or cumulative sums of certain values over a sequence of rows. One useful function that can help achieve this is FIRST_VALUE.

FIRST_VALUE is an analytic function in SQL that allows us to retrieve the first value in an ordered partition of rows. By utilizing this function along with window functions, we can effectively calculate running totals and cumulative sums in SQL queries.

To demonstrate how to use FIRST_VALUE for calculating running totals, let’s assume we have a table called sales with the following columns: transaction_id, customer_id, and amount.

SELECT 
  transaction_id,
  customer_id,
  amount,
  SUM(amount) OVER (ORDER BY transaction_id) AS running_total
FROM
  sales;

In the above query, we are selecting the transaction_id, customer_id, amount columns from the sales table. We then use the SUM function along with the OVER clause to calculate the running total of the amount column. The ORDER BY clause determines the order in which the running total calculation is performed.

Similarly, we can use FIRST_VALUE to calculate cumulative sums in SQL. Let’s modify our previous query to include cumulative sums instead of running totals.

SELECT 
  transaction_id,
  customer_id,
  amount,
  SUM(amount) OVER (ORDER BY transaction_id) AS running_total,
  SUM(amount) OVER (ORDER BY transaction_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM
  sales;

In the updated query, we add another column cumulative_sum which uses the SUM function along with the OVER clause. The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW specifies that we want the cumulative sum from the start of the ordered partition to the current row.

With the help of FIRST_VALUE and window functions, we can easily calculate running totals and cumulative sums in SQL. This is particularly useful when analyzing time-series data or when dealing with any data that requires aggregating values over a sequence of rows.

By understanding and utilizing these SQL functions effectively, we can perform advanced calculations and analysis on our data with ease.

References