How to get the cumulative sum of an aggregate column?

Question

I have this query in BigQuery that returns the representation of total contributing_factor_vehicle_1

SELECT
    TBL_TOTAL.contributing_factor_vehicle_1,
    TBL_TOTAL.TOTAL,
    (TBL_TOTAL.TOTAL / SUM(TBL_TOTAL.TOTAL) OVER ()) * 100  AS PERCENTAGE
FROM 
    (SELECT 
         contributing_factor_vehicle_1,
         COUNT(contributing_factor_vehicle_1) AS TOTAL
     FROM 
         `bigquery-public-data.new_york_mv_collisions.nypd_mv_collisions` 
     WHERE 
         borough = 'BROOKLYN' 
         AND contributing_factor_vehicle_1 <> 'Unspecified'
     GROUP BY 
         contributing_factor_vehicle_1
     ORDER BY 
         TOTAL DESC) TBL_TOTAL
ORDER BY 
    TOTAL DESC

Output:

contributing_factor_vehicle_1	TOTAL	PERCENTAGE
Driver Inattention/Distraction	65427	28.913538237178777
Failure to Yield Right-of-Way	25831	11.415250679452903
Backing Unsafely	16384	7.240426895286917
Following Too Closely	12605	5.570408997503148
Passing Too Closely	10875	4.805886382217116

Now I need to get the cumulative PERCENTAGE to make a pareto analysis: How do I achieve it please? Is it possible to use the column PERCENTAGE in a window function again?

contributing_factor_vehicle_1	TOTAL	PERCENTAGE	PERCENTAGE CUM
Driver Inattention/Distraction	65427	28.91%	28.91%
Failure to Yield Right-of-Way	25831	11.42%	40.33%
Backing Unsafely	16384	7.24%	47.57%
Following Too Closely	12605	5.57%	53.14%
Passing Too Closely	10875	4.81%	57.95%

user_5221944 · Answer

Just add one more line into the outer SELECT as in below example

SELECT
  TBL_TOTAL.contributing_factor_vehicle_1,
  TBL_TOTAL.TOTAL,
  ROUND((TBL_TOTAL.TOTAL/SUM(TBL_TOTAL.TOTAL) OVER ())* 100, 2)  AS PERCENTAGE,
  ROUND(((SUM(TBL_TOTAL.TOTAL) OVER (ORDER BY TOTAL DESC))/SUM(TBL_TOTAL.TOTAL) OVER ())* 100, 2) AS PERCENTAGE_CUM
FROM 
(
    SELECT 
    contributing_factor_vehicle_1,
    COUNT(contributing_factor_vehicle_1) AS TOTAL
    FROM `bigquery-public-data.new_york_mv_collisions.nypd_mv_collisions` 
    WHERE borough = 'BROOKLYN' AND contributing_factor_vehicle_1 <> 'Unspecified'
    GROUP BY contributing_factor_vehicle_1
    ORDER BY TOTAL DESC 
) TBL_TOTAL

ORDER BY TOTAL DESC

with output

How to get the cumulative sum of an aggregate column?

1 Answers