SQLAlchemy func.count on boolean column

by

Using aggregate functions in a HAVING clause is very much legal, since HAVING eliminates group rows. Conditional counting can be achieved either by using the property that NULLs don’t count:

count(expression) … number of input rows for which the value of expression is not null

or if using PostgreSQL 9.4 or later, with the aggregate FILTER clause: 

count(*) FILTER (WHERE something > 0)

You could also use a sum of ones (and zeros).

PostgreSQL >= 9.4 and SQLAlchemy >= 1.0.0

Using a filtered aggregate function:

.having(func.count(1).filter(Question.accepted) >
        func.count(1).filter(not_(Question.accepted)))

Older PostgreSQL and/or SQLAlchemy

The SQL analog for “if” is either CASE expression or in this case nullif() function. Both of them can be used together with the fact that NULLs don’t count:

from sqlalchemy import case

...

.having(func.count(case([(Question.accepted, 1)])) >
        func.count(case([(not_(Question.accepted), 1)])))

or:

.having(func.count(func.nullif(Question.accepted, False)) >
        func.count(func.nullif(Question.accepted, True)))

Using nullif() can be a bit confusing as the “condition” is what you don’t want to count. You could device an expression that would make the condition more natural, but that’s left for the reader. These 2 are more portable solutions, but on the other hand the FILTER clause is standard, though not widely available.

More Related Contents:

  1. How to write DataFrame to postgres table
  2. CS50: LIKE operator, variable substitution with % expansion
  3. SQLAlchemy create_all() does not create tables
  4. Executing multiple statements with Postgresql via SQLAlchemy does not persist changes
  5. How to create an SQL View with SQLAlchemy?
  6. Generate sql with subquery as a column in select statement using SQLAlchemy
  7. Updates to JSON field don’t persist to DB
  8. Complex foreign key constraint in SQLAlchemy
  9. Connection problems with SQLAlchemy and multiple processes
  10. How to upsert pandas DataFrame to PostgreSQL table?
  11. SQLAlchemy multiple foreign keys in one mapped class to the same primary key
  12. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  13. SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql
  14. Strange SQLAlchemy error message: TypeError: ‘dict’ object does not support indexing
  15. Creating partial unique index with sqlalchemy on Postgres
  16. How to set connection timeout in SQLAlchemy
  17. SQLAlchemy: filtering on values stored in nested list of the JSONB field
  18. How to prevent UUID primary key for new SQLAlchemy objects being created with the same value
  19. Python Postgres psycopg2 ThreadedConnectionPool exhausted
  20. sqlalchemy: how to join several tables by one query?
  21. Serve image stored in SQLAlchemy LargeBinary column
  22. ImportError: No module named MySQLdb
  23. How do I select literal values in an sqlalchemy query?
  24. Get inserted key before commit session
  25. SQLAlchemy default DateTime
  26. Setting SQLAlchemy autoincrement start value
  27. pandas to_sql all columns as nvarchar
  28. How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject
  29. Deploying Django to Heroku (Psycopg2 Error)
  30. add column to SQLAlchemy Table

Leave a Comment