How do I calculate the accumulative percentage?

Question

I have a table that consists of a customer ID, and the number of hours it took to place an order since they first registered.

An example would be:

UserId                                |   TimeToPay
---------------------------------------------------
2DD6ABBB-C9A4-4373-B188-312DB8222859  |   0
C7438620-6431-4C13-B335-AA1A3E314C58  |   55
6AG22103-62B0-47A0-BE3F-7AE1A7A4C3B7  |   30
300A2E02-0799-47BB-BF36-070706F98149  |   8
43382839-E897-4E5F-A955-C9DDAF9B424B  |   0

In the above example, 2 customers have placed an order within an hour of ordering something, and after 55 hours, all customers have placed an order. This table does not contain customers that have not placed an order yet. I am trying to create a query that shows cumulative percentages of how many customers have placed an order in what timespan. So my prefered output would be:

Hours  |  PercentageOfCustomers
-------------------------------
0      |  40
8      |  60
30     |  80
55     |  100

However, when I use answers like this or this one, I don't get cumulative percentages. How do I get my desired output?

score 3 · Accepted Answer · edited Oct 21 '16 at 14:17

3

You can use a windowed COUNT(*) to get a rolling total, and divide that by the number of total customers:

Select  Distinct TimeToPay As Hours, 
        ((Count(*) Over (Order By TimeToPay Asc) * 1.0) / 
            (Count(*) Over (Order By (Select Null)) * 1.0)) 
            * 100 As PercentageOfCustomers
From    Test
Order by Hours

edited Oct 21 '16 at 14:17

yesman

7,165
15
52
117

answered Oct 21 '16 at 14:09

Siyual

16,415
8
44
58

Works great, thanks! All I had to do was add an ORDER BY clause. I edited your question to reflect what I used. – yesman Oct 21 '16 at 14:15

score 0 · Answer 2 · answered Oct 21 '16 at 14:22

Try This:

DECLARE @main_table TABLE ( UserId INT, TimeToPay INT)

INSERT INTO @main_table VALUES(1,0),(2,55),(3,30),(4,8),(5,0),(6,30),(7,30)

DECLARE @total INT = ( SELECT COUNT(col) FROM 
( SELECT 'Z' col FROM @main_table GROUP BY TimeToPay )A GROUP BY col )

SELECT TimeToPay, (COUNT(TimeToPay)*100)/@total Percentage FROM @main_table
GROUP BY TimeToPay

Hope it helps. :)

How do I calculate the accumulative percentage?

2 Answers2