query to insert records to another table by calculating the percentage on a column

I have 2 Postgres tables with the following structure:

Table "public.tmp"
      Column       |          Type           | Collation | Nullable | Default 
-------------------+-------------------------+-----------+----------+---------
 MY_SL             | character varying(50)   |           |          | 
 Release           | character varying(50)   |           |          | 
 HOSTNAME          | character varying(50)   |           | not null | 
 UN NO.            | character varying(50)   |           |          | 
 STATUS            | character varying(50)   |           |          | 
 S_DATE            | character varying(50)   |           | not null | 

Table "public.mo"
      Column       |          Type           | Collation | Nullable |                 Default                 
-------------------+-------------------------+-----------+----------+-----------------------------------------
 id                | integer                 |           | not null | nextval('mo_id_seq'::regclass)
 HOSTNAME          | character varying(50)   |           | not null | 
 UN NO.            | character varying(50)   |           |          | 
 STATUS            | character varying(50)   |           |          | 
 S_DATE            | character varying(50)   |           | not null | 
 compliant_status  | character varying(50)   |           | not null |
 C_PERCENT         | character varying(50)   |           | not null |

Lets say I have data in the tmp table as below:

Table: tmp

 MY_SL | Release | HOSTNAME   |  UN NO.|  STATUS   |      S_DATE    
------------+-------------+-----------+----------------------+------------------
 2     | 1       | RhelTest   | 7:1:8  | COMPLIANT | 2020-08-26
 12    | 1       | RhelTest   | 7:1:9  | COMPLIANT | 2020-08-26
 22    | 2       | RhelTest   | 7:2:1  | COMPLIANT | 2020-08-26
 4     | 1       | RhelTest   | 7:2:10 | NC        | 2020-08-26
 12    | 1       | RhelTest   | 7:1:9  | COMPLIANT | 2020-08-26
 22    | 2       | RhelTest   | 7:2:1  | COMPLIANT | 2020-08-26
 12    | 1       | RhelTest   | 7:1:9  | NC        | 2020-08-26
 22    | 2       | RhelTest   | 7:2:1  | COMPLIANT | 2020-08-26
 11    | 2       | RhelTest   | 7:2:11 | NC        | 2020-08-26
 1     | 3       | Demo1      | 7:2:11 | NC        | 2020-08-26
 23    | 3       | Demo1      | 7:2:11 | NC        | 2020-08-26
 1     | 3       | Demo1      | 7:2:11 | NC        | 2020-08-26
 23    | 3       | Demo1      | 7:2:11 | NC        | 2020-08-26
 1     | 3       | Demo1      | 7:2:11 | NC        | 2020-08-26
 23    | 3       | Demo1      | 7:2:11 | NC        | 2020-08-26
 333   | 3       | Demo2      | 7:2:11 | COMPLIANT | 2020-08-26
 333   | 3       | Demo2      | 7:2:11 | COMPLIANT | 2020-08-26
 333   | 3       | Demo2      | 7:2:11 | COMPLIANT | 2020-08-26
 333   | 3       | Demo2      | 7:2:11 | COMPLIANT | 2020-08-26
 333   | 3       | Demo3      | 7:2:11 | COMPLIANT | 2020-08-26
 333   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26
 333   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26
 333   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26
 333   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26
 432   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26 
 333   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26
 111   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26
 333   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26
 321   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26
 564   | 3       | Demo3      | 7:2:11 | NC        | 2020-08-26
 958   | 3       | Demo3      | 7:2:11 | COMPLIANT | 2020-08-26

I have a script below that inserts the data from public.tmp to public.mo table based on the criteria that

• if the column STATUS has mix values (COMPLIANT and NC) for a particular HOSTNAME then compliant_status is PARTIAL and
• if all values are COMPLIANT for a particular HOSTNAME then compliant_status is COMPLIANT and
• if all values are NC for a particular HOSTNAME then compliant_status is NON_COMPLIANT

insert into mo ("HOSTNAME","UN NO.","STATUS","S_DATE", compliant_status)
        select "HOSTNAME","UN NO.","STATUS","S_DATE",
         case
                when min_host_status <> max_host_status then 'PARTIAL'
                when min_host_status = 'NC' then 'NON_COMPLIANT'
                else min_host_status
         end
        from (
         select t.*,
                min("STATUS") over(partition by "HOSTNAME") min_host_status,
                max("STATUS") over(partition by "HOSTNAME") max_host_status
         from tmp_aix t
         where "STATUS" != 'NOT_APPLICABLE'
) t;

Now I want to display compliant_status based on the following conditions:

1. If the no. of COMPLIANT values in STATUS column for a particular HOSTNAME is greater than 80% then the compliant_status is COMPLIANT.
2. If the no. of COMPLIANT values in STATUS column for a particular HOSTNAME is less than 80% then the compliant_status is NON_COMPLIANT.

Iam expecting the final output as below based on the above data:

HOSTNAME: RhelTest, COMPLIANT %: 88.88%, compliant_status: COMPLIANT

HOSTNAME: Demo1 , COMPLIANT %: 0%, compliant_status: NON_COMPLIANT

HOSTNAME: Demo2 , COMPLIANT %: 100%, compliant_status: COMPLIANT

HOSTNAME: Demo3 , COMPLIANT %: 16.67%, compliant_status: NON_COMPLIANT

Final expected table:

Table: public.mo

  HOSTNAME  |  UN NO.     |  STATUS   | S_DATE  | compliant_status  | C_PERCENT
------------+-------------+-----------+----------------------+------------------
 RhelTest   | 7:1:8  | COMPLIANT | 2020-08-26   | COMPLIANT         | 88.88
 RhelTest   | 7:1:9  | COMPLIANT | 2020-08-26   | COMPLIANT         | 88.88
 RhelTest   | 7:2:1  | COMPLIANT | 2020-08-26   | COMPLIANT         | 88.88
 RhelTest   | 7:2:10 | COMPLIANT | 2020-08-26   | COMPLIANT         | 88.88
 RhelTest   | 7:1:9  | COMPLIANT | 2020-08-26   | COMPLIANT         | 88.88
 RhelTest   | 7:2:1  | COMPLIANT | 2020-08-26   | COMPLIANT         | 88.88
 RhelTest   | 7:1:9  | NC        | 2020-08-26   | COMPLIANT         | 88.88
 RhelTest   | 7:2:1  | COMPLIANT | 2020-08-26   | COMPLIANT         | 88.88
 RhelTest   | 7:2:11 | COMPLIANT | 2020-08-26   | COMPLIANT         | 88.88
 Demo1      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 0
 Demo1      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 0
 Demo1      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 0
 Demo1      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 0
 Demo1      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 0
 Demo1      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 0
 Demo2      | 7:2:11 | COMPLIANT | 2020-08-26   | COMPLIANT         | 100
 Demo2      | 7:2:11 | COMPLIANT | 2020-08-26   | COMPLIANT         | 100
 Demo2      | 7:2:11 | COMPLIANT | 2020-08-26   | COMPLIANT         | 100
 Demo2      | 7:2:11 | COMPLIANT | 2020-08-26   | COMPLIANT         | 100
 Demo3      | 7:2:11 | COMPLIANT | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | NC        | 2020-08-26   | NON_COMPLIANT     | 16.67
 Demo3      | 7:2:11 | COMPLIANT | 2020-08-26   | NON_COMPLIANT     | 16.67

Answer

If I follow you correctly, you can use window functions:

select m.*,
    100 * avg((compliant_status = 'COMPLIANT')::int) over(partition by hostname) c_percent
    case when avg((compliant_status = 'COMPLIANT')::int) over(partition by hostname) >= 0.8
        then 'COMPLIANT'
        else 'NON_COMPLIANT'
    end as final_compliant_status
from mo m