How do I find percentages of a column using Hive/Presto

Question

Let's say I have a table that looks like:

Reason          | Duration
Eating              40
Drinking            60
Everything Else     100

How do I get a table like this:

Reason          | Duration | Duration Percent
Eating              40             20
Drinking            60             30
Everything Else     100            50

Answer 1

You can use a window function to compute the total:

SELECT reason, duration, (duration * 100.0) / sum(duration) OVER () pct
FROM (
  VALUES
    ('eating', 40),
    ('drinking', 60),
    ('other', 100)
) AS t (reason, duration)

Note that Presto (per the SQL standard) performs integer division, so it is necessary to convert one of the values to a double or decimal (otherwise the result will be zero).

  reason  | duration | pct  
----------+----------+------
 eating   |       40 | 20.0 
 drinking |       60 | 30.0 
 other    |      100 | 50.0 
(3 rows)