CODEWITHSUNDEEP
sqlamazon-redshift
MrDuk
MrDuk

Reputation: 18242

How to calculate the percent of the overall total sum, per entry?

I have a table that stores requests per title. I can query it with the following:

SELECT lower(btrim(environment)) as environment,
       lower(btrim(title)) as title,
       SUM(count) as requests
       FROM api_data_analytics
         WHERE timestamp BETWEEN '2019-10-01 00:00:00.000000' AND '2019-11-01 00:00:00.000000'
           AND environment = 'production'
         GROUP BY 1, 2;

This produces:

env:        title:     requests:
production    a1       18194440913
production    b1       2425465014
production    c1       265733967
production    d1       92586792
production    e1       57150246

I'd like to be able to execute a query that gives me the % of the total requests for each title, something like:

env:        title:     pct_total:
production    a1       85.0
production    b1       13.2
production    c1       1.4
production    d1       0.3
production    e1       0.1

What's the easiest way to get at this data?

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use window functions:

SELECT lower(btrim(environment)) as environment,
       lower(btrim(title)) as title,
       SUM(count) as requests,
       SUM(count) * 100.0 / SUM(SUM(count)) OVER () as percent_total
FROM api_data_analytics
WHERE timestamp BETWEEN '2019-10-01 00:00:00.000000' AND '2019-11-01 00:00:00.000000' AND
     environment = 'production'
GROUP BY 1, 2;

Upvotes: 5

Related Questions