sql - How to percentage for unique count of string in redshift?

Question

I have my data in the below format:

TASK, STATUS
MKw11sk, OPEN
JIKL, CLOSED
pp,DELETED
qwert,PRESENT
DDF,OPEN

I am trying to find the percent by status. i wants to know the percentage for status column

i'm newbie to this! Normally i can calculate percentage for integer type values and i dont have to calculate this unique count of string

question from:https://stackoverflow.com/questions/65599615/how-to-percentage-for-unique-count-of-string-in-redshift

Answer 1

Was the question What is the percentage for each status value in the table? You can use window function and get the correct value for each row, like this:

with input (TASK, STATUS) as (
SELECT 'MKw11sk', 'OPEN' UNION ALL
SELECT 'JIKL', 'CLOSED' UNION ALL
SELECT 'pp', 'DELETED' UNION ALL
SELECT 'qwert', 'PRESENT' UNION ALL
SELECT 'DDF', 'OPEN'
    )
select *,
   count(STATUS) over (partition by STATUS) * 100 / count(STATUS) over () as status_percent
from input;

returns

task	status	status_percent
JIKL	CLOSED	20
MKw11sk	OPEN	40
DDF	OPEN	40
qwert	PRESENT	20
pp	DELETED	20