1

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.

xuxu_chase
  • 31
  • 8
  • I tried this method, it works: .having(func.count(case([(company_map.STATUS, 1)])) < func.count(company_map.C_ID)) – xuxu_chase Sep 12 '17 at 04:00

1 Answers1

2

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.

xuxu_chase
  • 31
  • 8
  • And if you're using suitable versions of Postgresql and SQLAlchemy, you could also just `func.count(1).filter(company_map.STATUS)`, which is a bit easier on the eyes. – Ilja Everilä Sep 12 '17 at 06:14
  • @Ilja Everilä Thx. I tried that, it return count have no attribute 'filter'. I think maybe my SQLAlchemy is old version. But count(1).filter() is much nicer. – xuxu_chase Sep 12 '17 at 10:20