CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
xuxu_chase
xuxu_chase

Reputation: 31

SQLAlchemy - count status is true

I have two tables in db. One is named company, the other named company_map. company table like:

C_ID NAME  Contact
1    a     12334   
2    b     12335
3    c     12336
4    d     12337
5    e     12338

company_map table like:

M_ID C_ID  STATUS
1     1     True   
2     1     False
3     1     True
4     3     True
5     3     True

I need to count the number which STATUS is true in the company_map table, group by the C_ID. For example, I need to get the number of C_ID is 1 and its STATUS is true, it should be 2. I can get the number of C_ID now, by use func.count(company_map.C_ID), it will get 3. But how to count STATUS == true()? I try some method, but none is work.

Upvotes: 1

Views: 1010

Answers (1)

xuxu_chase
xuxu_chase

Reputation: 31

I got idea from SQLAlchemy func.count on boolean column

.having(func.count(case([(company_map.STATUS, 1)])) < func.count(company_map.C_ID))

If your SQLAlchemy is latest version, can use 

.having(func.count(1).filter(company_map.STATUS))

which is cleaner than the old.

Upvotes: 2

Related Questions