CODEWITHSUNDEEP
postgresql
2Real
2Real

Reputation: 4431

Postgres: How to get aggregates of multiple columns

I want to implement filtered navigation with postgres, but I'm not sure how to return the count of each value in multiple fields from the result set.

Example Schema:

id, name, status

for this query i'd want to see something like (doesn't have to mimic this structure):

name: [(Bob, 20), (Joe, 15), (Sue, 5)]
status[(active, 15), (inactive, 25)]

Upvotes: 0

Views: 232

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125394

Check Grouping Sets

with t (name, status) as (values
    ('Bob', 'active'),('Bob', 'active'),
    ('Joe', 'inactive'),('Joe', 'active'),('Joe', 'active')
)
select json_object_agg(case g when 1 then 'name' else 'status' end,a)
from (
    select jsonb_agg(jsonb_build_object(coalesce(name,status), total)) as a, g
    from (
        select name, status, count(*) as total, grouping(name,status) as g
        from t
        group by grouping sets ((name),(status))
    ) s
    group by g
) s
;
                                  json_object_agg                                   
------------------------------------------------------------------------------------
 { "name" : [{"Bob": 2}, {"Joe": 3}], "status" : [{"active": 4}, {"inactive": 1}] }

Upvotes: 2

Related Questions