CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Eino Gourdin
Eino Gourdin

Reputation: 4507

SQLAlchemy func.count on boolean column

How can I count easily the number of rows where a particular column is true and the number where it is false ?

I can't (or can I ?) run the query with count() because I'm embedding this count in a having() clause, like :

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

but with the above way, the function counts every line on both sides of the inequality.

I tried something like this

.having(func.count(func.if_(Question.accepted, 1, 0)) >
        func.count(func.if_(Question.accepted, 0, 1)))

But I get an error

function if(boolean, integer, integer) does not exist

(seems it doesn't exist in postgresql).

How can I count easily the number of rows where column is true and false ?

Upvotes: 8

Views: 10243

Answers (3)

Gary Mialaret
Gary Mialaret

Reputation: 31

Most flavors of SQL store booleans as 1-bit integers, and calling SUM on them properly gives you the number of positive booleans/True rows. In your case, you can simply do:

func.sum(Question.accepted)

If your SQL engine returns a SUM of booleans as a boolean (not sure there is any), you can use expression function cast() to force it into an integer instead of using func.count(case([(Question.accepted, 1)])):

func.sum(sqlalchemy.cast(Question.accepted, sqlalchemy.Integer))

Finally, if you want the sum as an integer on the Python side the expression function type_coerce() does the trick: 

sqlalchemy.type_coerce(func.sum(Question.accepted), sqlalchemy.Integer)

Upvotes: 2

Ilja Everilä
Ilja Everilä

Reputation: 52949

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.

Upvotes: 10

antonio_antuan
antonio_antuan

Reputation: 1313

You can use this query:

Session.query(func.sum(case([(Question.accepted == True, 1)], else_=0).label('accepted_number'))

And the same column will be for False value, but with False in condition

Or, you can use window function:

Session.query(func.count(Question.id).over(partition_by=Question.accepted), Question.accepted).all()

The result will contain two rows (if there are only two possible values in Question.accepted), where the first column is the number of values, and the second is the values of 'accepted' column.

Upvotes: 5

Related Questions