Calculate Percentage in postgresql

Question

I have a query which returns the results

time  t_count  cumulative_t_count  zone  hour
_____ _____  _________________     ____  ____
1      10          10               1    1
2      20          30               1    1
3      30          60               1    1
4      60          120              1    1

My query will be like

select time,t_count,sum(t_count) over (order by time) as
   cumulative_t_count ,zone,hour from (select distinct
   time,count(distinct num_id) as t_count,zone,hour from (select * from
   public.table1 where day=1 and time>0 order by time)as A group by
   time,hour,zone order by hour,zone )B where zone=1 and hour=1; 

Now I want an additional column which shows the percentage of cumulative count like this

time  t_count  cumulative_t_count  zone  hour   percentage_cumulative count
_____ _____  _________________     ____  ____   ____________________________
1      10          10               1     1                  10%
2      20          30               1     1                  30%
3      30          60               1     1                  60%
4      40          100              1     1                  100%

I tried with

 select time,t_count,sum(t_count) over (order by time) as
       cumulative_t_count ,cumulative_t_count/sum(t_count) as percentage_cumulative count,zone,hour from (select distinct
       time,count(distinct num_id) as t_count,zone,hour from (select * from
       public.table1 where day=1 and time>0 order by time)as A group by
       time,hour,zone order by hour,zone )B where zone=1 and hour=1; 

But it did not work.Any help is appreciated.

Answer 1

In order to calculate the percentage of cumulative count each row contributes to the total cumulative count, you should run another SQL on your result set and calculate percentage for each row.

I believe query should answer the question you're asking for.

SELECT time,
t_count,
cumulative_t_count,
zone,
hour,
(((cumulative_t_count)/(sum(cumulative_t_count) OVER (ORDER BY time))) * 100) AS percentage_cumulative_count
FROM
(SELECT
  time,
  t_count,
  SUM(t_count) OVER (ORDER BY time) AS cumulative_t_count,
  zone,
  hour 
 FROM
  (
    SELECT DISTINCT
      time,
      COUNT(DISTINCT num_id) AS t_count,
      zone,
      hour
     FROM
      (
        SELECT
          * 
        FROM
          public.table1 
        WHERE
          day = 1 
          AND time > 0 
        ORDER BY
          time
      )
      AS a 
    GROUP BY
      time,
      hour,
      zone 
    ORDER BY
      hour,
      zone 
  ) b 
WHERE
  zone = 1 
  AND hour = 1) x;

Answer 2

Find the maximum t_count value and divide each value by the max value to find percentage

Try this

SELECT time,
       t_count,
       Sum(t_count) OVER(ORDER BY time)           AS cumulative_t_count,
       zone,
       hour,
       ( t_count / Max(t_count) OVER() ) * 100 AS percentage_cumulative,
...