How to Calculate a Cumulative Percentage in SQL

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

A fun report to write is to calculate a cumulative percentage. For example, when querying the Sakila database, we might want to calculate the percentage of our total revenue at any given date.
The result might look like this:

Notice the beautifully generated data. Or as raw data:

payment_date |amount |percentage
————-|——–|———-
2005-05-24 |29.92 |0.04
2005-05-25 |573.63 |0.90
2005-05-26 |754.26 |2.01
2005-05-27 |685.33 |3.03
2005-05-28 |804.04 |4.22
2005-05-29 |648.46 |5.19
2005-05-30 |628.42 |6.12
2005-05-31 |700.37 |7.16

2005-08-18 |2710.79 |79.59
2005-08-19 |2615.72 |83.47
2005-08-20 |2723.76 |87.51
2005-08-21 |2809.41 |91.67
2005-08-22 |2576.74 |95.49
2005-08-23 |2523.01 |99.24
2005-08-24 |514.18 |100.00

In other words, at the beginning of our timeline, we’ve made 0% revenue, and then that percentage increases over time, until we reach 100% of our revenue at the end of our timeline.
How to do it?
We’re going to do it in two steps. Our PAYMENT table has a PAYMENT_DATE column, which is really a timestamp, i.e. the exact amount in time when we received a payment. We can query the table to see its data (I will be using PostgreSQL syntax in this post):

SELECT
payment_date,
amount
FROM payment
ORDER BY payment_date;

This yields:

payment_date |amount
——————–|——
2005-05-24 22:53:30 |2.99
2005-05-24 22:54:33 |2.99
2005-05-24 23:03:39 |3.99
2005-05-24 23:04:41 |4.99
2005-05-24 23:05:21 |6.99
2005-05-24 23:08:07 |0.99
2005-05-24 23:11:53 |1.99
2005-05-24 23:31:46 |4.99
2005-05-25 00:00:40 |4.99
2005-05-25 00:02:21 |5.99
2005-05-25 00:09:02 |8.99
2005-05-25 00:19:27 |4.99
2005-05-25 00:22:55 |6.99

Now we could calculate that percentage on this timeline, but that wouldn’t be terribly interesting. We’re interested in the cumulative revenue per date, so let’s run a classic GROUP BY:

SELECT
CAST(payment_date AS DATE),
sum(amount) AS amount
FROM payment
GROUP BY CAST(payment_date AS DATE)
ORDER BY CAST(payment_date AS DATE);

This yields the first two columns of our desired result:

payment_date |amount
————-|——-
2005-05-24 |29.92
2005-05-25 |573.63
2005-05-26 |754.26
2005-05-27 |685.33
2005-05-28 |804.04
2005-05-29 |648.46
2005-05-30 |628.42
2005-05-31 |700.37

2005-08-18 |2710.79
2005-08-19 |2615.72
2005-08-20 |2723.76
2005-08-21 |2809.41
2005-08-22 |2576.74
2005-08-23 |2523.01
2005-08-24 |514.18

Now about that percentage. The formula in pseudo SQL is this:

cumulative_percentage[N] = SUM(amount[M

X ITM Cloud News

Emily

Next Post

The Difference Between SQL’s JOIN .. ON Clause and the Where Clause

Sun Nov 24 , 2019
Spread the love          A question that is frequently occurring among my SQL training‘s participants is: What’s the difference between putting a predicate in the JOIN .. ON clause and the WHERE clause? I can definitely see how that’s confusing some people, as there seems to be no difference at first sight, […]
X- ITM

Cloud Computing – Consultancy – Development – Hosting – APIs – Legacy Systems

X-ITM Technology helps our customers across the entire enterprise technology stack with differentiated industry solutions. We modernize IT, optimize data architectures, and make everything secure, scalable and orchestrated across public, private and hybrid clouds.

This image has an empty alt attribute; its file name is x-itmdc.jpg

The enterprise technology stack includes ITO; Cloud and Security Services; Applications and Industry IP; Data, Analytics and Engineering Services; and Advisory.

Watch an animation of  X-ITM‘s Enterprise Technology Stack

We combine years of experience running mission-critical systems with the latest digital innovations to deliver better business outcomes and new levels of performance, competitiveness and experiences for our customers and their stakeholders.

X-ITM invests in three key drivers of growth: People, Customers and Operational Execution.

The company’s global scale, talent and innovation platforms serve 6,000 private and public-sector clients in 70 countries.

X-ITM’s extensive partner network helps drive collaboration and leverage technology independence. The company has established more than 200 industry-leading global Partner Network relationships, including 15 strategic partners: Amazon Web Services, AT&T, Dell Technologies, Google Cloud, HCL, HP, HPE, IBM, Micro Focus, Microsoft, Oracle, PwC, SAP, ServiceNow and VMware

.

X ITM