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,
x
| 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_countfrom table_namegroup by namegives 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 |Advertisement
Answer
Use grouping sets:
select name, gender, count(*) as value_countfrom my_tablegroup 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_countfrom ( 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)