Value counts for every field of a table

In PostgresSQL, I am trying to get the value counts of every field and value pair. For illustration, I have 2 columns, but I really have 100 columns. So, I need a programmatic solution.

For a table,

| id | name  | gender |
-----------------------
| 1  | john  | male   |
| 2  | susan | female |
| 3  | john  | male   |
| 4  | john  | male   |
| 5  | susan | female |
| 6  | john  | male   |
| 7  | susan | female |
| 8  | julie | female |
| 9  | julie | female |

I know that

select name, count(*) as value_count
from table_name
group by name

gives me what I want for one column:

| name  | value_count |
-----------------------
| john  | 4           |
| susan | 3           |
| julie | 2           |

But how can I programmatically iterate through all the columns and generate this, for example:

| column_name | value_name | value_count |
------------------------------------------
| name        | john       | 4           |
| name        | susan      | 3           |
| name        | julie      | 2           |
| gender      | male       | 4           |
| gender      | female     | 5           |

Answer

Use grouping sets:

select name, gender, count(*) as value_count
from my_table
group by grouping sets ((name), (gender))

 name  | gender | value_count 
-------+--------+-------------
 john  |        |           4
 julie |        |           2
 susan |        |           3
       | female |           5
       | male   |           4
(5 rows)

If you are determined to get the result in the mentioned format:

select 
    case when gender is null then 'name' else 'gender' end as column_name,
    coalesce(gender, name) as value_name,
    value_count
from (
    select name, gender, count(*) as value_count
    from my_table
    group by grouping sets ((name), (gender))
    ) s

 column_name | value_name | value_count 
-------------+------------+-------------
 name        | john       |           4
 name        | julie      |           2
 name        | susan      |           3
 gender      | female     |           5
 gender      | male       |           4
(5 rows)