count on multiple boolean columns

My Current Table looks as follows

public.transactions

storeId     FeatureA  FeatureB  FeatureC  Details
123         true      false     false     ... (JSON)
123         false     false     false
123         true      false     true

basically the transaction table tracks the specific feature that triggered the transaction. I need to grab the count for each feature for a specific id, something like this:

storeId     FeatureA  FeatureB  FeatureC
123         2         0         1

I’ve been getting the count doing 3 individual counts

Select *
FROM public.transactions
where "storeId" = 123 AND "FeatureA" = true

but the seems really inefficient.

Answer

Do you just want conditional aggregation? Postgres makes this easy by supporting the filter clause:

select storeid,
       count(*) filter (where featureA) as num_featureA,
       count(*) filter (where featureB) as num_featureB,
       count(*) filter (where featureC) as num_featureC
from public.transactions t
group by storeid;

Advertisement

Answer