Distinct SQL is not working with UNNEST and ARRAY

Question

I have database called Data like following :

ID | Name | Weakness1 | Weakness2 | Weakness3
_____________________________________________
 1 |  A   |    W1     |     W2    |    Null
 2 |  B   |    NULL   |     W2    |    W3
 3 |  C   |    W1     |     NULL  |    NULL
 4 |  A   |    W1     |     W2    |    W3
 5 |  A   |    W1     |     NULL  |    NULL

and I like to query so it can be displayed like :

 Name |    Weakness     | Frequency
___________________________________
  A   |   W1            |   3
  A   |   W2            |   2
  A   |   W3            |   1

Notice that same name can have different ID. So far I tried the code :

 Select DISTINCT Name, 
 UNNEST(ARRAY[weakness1,weakness2,weakness3] as Weakness,
 UNNESR(ARRAY[count(weakness1),count(weakness2),count(weakness2)])
 from data 
 where agentname like '%A%'
 GROUP by agentname,weakness1,weakness2,weakness3

I got a NULL row also displayed. COUNT(Weakness1)>0 is not allowed after 'where' and I got the weaknesses are redundant, though I already added 'DISTINCT'

so it display like :

 Name |    Weakness     | Frequency
___________________________________
  A   |   W1            |   1
  A   |                 |   0
  A   |   W3            |   1
  A   |   W1            |   2
  A   |   W2            |   1
  A   |   W2            |   1

Something wrong here? Thank you

Answer 1

Could you try something like this?

with data as (
  select name, weakness1 as weakness from tablename
  union all
  select name, weakness2 as weakness from tablename
  union all
  select name, weakness3 as weakness from tablename
)
select name, weakness, count(*)
from data
where name like '%A%' and weakness is not null
group by name, weakness

Result

| name | weakness | count |
|------|----------|-------|
|    A |       W1 |     3 |
|    A |       W3 |     1 |
|    A |       W2 |     2 |

Example: http://sqlfiddle.com/#!15/7e4aa/3

Alternate method:

select name, weakness, count(*) from (
  select name, unnest(array[weakness1, weakness2, weakness3]) as weakness
  from tablename
) t
where name like '%A%' and weakness is not null
group by name, weakness;

Example: http://sqlfiddle.com/#!15/7e4aa/6

Answer 2

SELECT NAME
    ,unnest(regexp_split_to_array(concat_ws(',', weak1, weak2, weak3), ',')) weakness
    ,count(unnest(regexp_split_to_array(concat_ws(',', weak1, weak2, weak3), ','))) frequency
FROM dat
WHERE NAME = 'A' -- or use WHERE NAME like '%A%'
GROUP BY 2,1

Answer 3

Find this

   select Name,Weakness1 as weakness,count(Weakness1) from data
     where name ='A' group by name,weakness1

     Union all

     select Name,Weakness2 as weakness,count(Weakness2) from data
     where name ='A' and Weakness2 ='W2' group by name,weakness2

     union all

     select Name,Weakness3 as weakness,count(Weakness3) from data
     where name ='A' and Weakness3 ='W3' group by name,weakness3