count on multiple boolean columns

Question

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.

Gordon Linoff · Accepted Answer · 2019-08-05 20:52:45Z

5

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;

answered Aug 5, 2019 at 20:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

forpas · Accepted Answer · 2019-08-05 20:54:35Z

3

With conditional aggregation:

select storeid,
  sum(case when featurea then 1 else 0 end) featurea,
  sum(case when featureb then 1 else 0 end) featureb,
  sum(case when featurec then 1 else 0 end) featurec
from tablename
group by storeid

answered Aug 5, 2019 at 20:54

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

count on multiple boolean columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related