blocks|key|1034134|text|您可以使用以下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1034135|Session.query(func.sum(case([(Question.accepted+==+True,+1)],+else_=0).label('accepted_number'))|code-block|syntax|javascript|1034136|而同一列的值为False，但条件为False。|1034137|或者，您可以使用窗口函数：|1034138|Session.query(func.count(Question.id).over(partition_by=Question.accepted),+Question.accepted).all()|1034139|结果将包含两行(如果Question.accepted中只有两个可能的值)，其中第一列是值的数目，第二列是“接受”列的值。|1034140|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You can use this query:

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

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

Or, you can use window function:

<pre><code>Session.query(func.count(Question.id).over(partition_by=Question.accepted), Question.accepted).all()
</code></pre>

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.

blocks|key|920917|text|在子句中使用聚合函数是非常合法的，因为HAVING消除了组行。条件计数可以通过使用NULL不计数的属性来实现：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|920918|count(expression)+...表达式值不为空的输入行数|blockquote|920919|或者，如果使用PostgreSQL+9.4或更高版本，则使用子句：|920920|count(*)+FILTER+(WHERE+something+>+0)|code-block|syntax|javascript|920921|您也可以使用1之和(和0)。|920922|PostgreSQL+>=+9.4和SQLAlchemy+>=+1.0.0|920923|使用过滤聚合函数|920924|.having(func.count(1).filter(Question.accepted)+>
++++++++func.count(1).filter(not_(Question.accepted)))|920925|旧PostgreSQL和/或SQLAlchemy|920926|"if“的SQL模拟是表达式，或者在本例中是nullif()函数。这两种方法都可以与NULL不算在内的事实一起使用：|920927|from+sqlalchemy+import+case

...

.having(func.count(case([(Question.accepted,+1)]))+>
++++++++func.count(case([(not_(Question.accepted),+1)])))|920928|或者：|920929|.having(func.count(func.nullif(Question.accepted,+False))+>
++++++++func.count(func.nullif(Question.accepted,+True)))|920930|使用nullif()可能有点混乱，因为“条件”是您不想计算的。您可以设置一个表达式，使条件更自然，但这是留给读者。这2是更可移植的解决方案，但另一方面，FILTER子句是标准的，尽管不是广泛可用。|920931|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/sql-select.html#SQL-HAVING|1|http://www.postgresql.org/docs/current/static/functions-aggregate.html|2|https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.4#Aggregate_FILTER_clause|3|http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES|4|https://stackoverflow.com/a/5396728/2681632|5|http://docs.sqlalchemy.org/en/latest/core/functions.html#sqlalchemy.sql.functions.FunctionElement.filter|6|http://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-CASE|7|http://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-NULLIF^0|J|6|15|4|1|2|0|0|0|H|0|H|1|0|7|J|2|U|2|3|0|0|6|7|4|0|0|2|6|5|0|0|0|M|8|16|4|B|3|6|M|8|7|0|0|0|0|2|8|24|6|0^^$0|@$1|2|3|4|5|6|7|1V|8|@$9|1W|A|1X|B|C]|$9|1Y|A|1Z|B|C]]|D|@$9|20|A|21|1|22]]|E|$]]|$1|F|3|G|5|H|7|23|8|@$9|24|A|25|B|C]]|D|@$9|26|A|27|1|28]]|E|$]]|$1|I|3|J|5|6|7|29|8|@]|D|@$9|2A|A|2B|1|2C]|$9|2D|A|2E|1|2F]]|E|$]]|$1|K|3|L|5|M|7|2G|8|@]|D|@]|E|$N|O]]|$1|P|3|Q|5|6|7|2H|8|@]|D|@$9|2I|A|2J|1|2K]]|E|$]]|$1|R|3|S|5|6|7|2L|8|@]|D|@]|E|$]]|$1|T|3|U|5|6|7|2M|8|@]|D|@$9|2N|A|2O|1|2P]]|E|$]]|$1|V|3|W|5|M|7|2Q|8|@]|D|@]|E|$N|O]]|$1|X|3|Y|5|6|7|2R|8|@]|D|@]|E|$]]|$1|Z|3|10|5|6|7|2S|8|@$9|2T|A|2U|B|C]|$9|2V|A|2W|B|C]]|D|@$9|2X|A|2Y|1|2Z]|$9|30|A|31|1|32]]|E|$]]|$1|11|3|12|5|M|7|33|8|@]|D|@]|E|$N|O]]|$1|13|3|14|5|6|7|34|8|@]|D|@]|E|$]]|$1|15|3|16|5|M|7|35|8|@]|D|@]|E|$N|O]]|$1|17|3|18|5|6|7|36|8|@$9|37|A|38|B|C]|$9|39|A|3A|B|C]]|D|@]|E|$]]|$1|19|3|-4|5|6|7|3B|8|@]|D|@]|E|$]]]|1A|$1B|$5|1C|1D|1E|E|$1F|1G]]|1H|$5|1C|1D|1E|E|$1F|1I]]|1J|$5|1C|1D|1E|E|$1F|1K]]|1L|$5|1C|1D|1E|E|$1F|1M]]|1N|$5|1C|1D|1E|E|$1F|1O]]|1P|$5|1C|1D|1E|E|$1F|1Q]]|1R|$5|1C|1D|1E|E|$1F|1S]]|1T|$5|1C|1D|1E|E|$1F|1U]]]]

Using aggregate functions in a <a href="http://www.postgresql.org/docs/current/static/sql-select.html#SQL-HAVING" rel="noreferrer"><code>HAVING</code> clause</a> is very much legal, since <code>HAVING</code> eliminates group rows. Conditional counting can be achieved either by using the property that <code>NULL</code>s don't count:

<blockquote>
 <a href="http://www.postgresql.org/docs/current/static/functions-aggregate.html" rel="noreferrer"><code>count(expression)</code></a> ... number of input rows for which the value of expression is not null
</blockquote>

or if using <a href="https://wiki.postgresql.org/wiki/What&#39;s_new_in_PostgreSQL_9.4#Aggregate_FILTER_clause" rel="noreferrer">PostgreSQL 9.4 or later</a>, with the <a href="http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES" rel="noreferrer">aggregate <code>FILTER</code> clause</a>:


<pre><code>count(*) FILTER (WHERE something &gt; 0)
</code></pre>

You could also use a <a href="https://stackoverflow.com/a/5396728/2681632">sum of ones (and zeros)</a>.

<h2>PostgreSQL >= 9.4 and SQLAlchemy >= 1.0.0</h2>

Using a <a href="http://docs.sqlalchemy.org/en/latest/core/functions.html#sqlalchemy.sql.functions.FunctionElement.filter" rel="noreferrer">filtered aggregate function</a>:

<pre><code>.having(func.count(1).filter(Question.accepted) &gt;
 func.count(1).filter(not_(Question.accepted)))
</code></pre>

<h2>Older PostgreSQL and/or SQLAlchemy</h2>

The SQL analog for "if" is either <a href="http://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-CASE" rel="noreferrer"><code>CASE</code> expression</a> or in this case <a href="http://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-NULLIF" rel="noreferrer"><code>nullif()</code></a> function. Both of them can be used together with the fact that <code>NULL</code>s don't count:

<pre><code>from sqlalchemy import case

...

.having(func.count(case([(Question.accepted, 1)])) &gt;
 func.count(case([(not_(Question.accepted), 1)])))
</code></pre>

or:

<pre><code>.having(func.count(func.nullif(Question.accepted, False)) &gt;
 func.count(func.nullif(Question.accepted, True)))
</code></pre>

Using <code>nullif()</code> 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 <code>FILTER</code> clause is standard, though not widely available.

blocks|key|922254|text|大多数类型的SQL将布尔值存储为1位整数，并在它们上正确地调用和，从而给出正布尔/真行的数量。在您的例子中，您可以简单地做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|922255|func.sum(Question.accepted)|code-block|syntax|javascript|922256|如果SQL引擎以布尔值的形式返回布尔值之和(不确定有布尔值)，则可以使用表达式函数cast()将其强制转换为整数，而不是使用func.count(case([(Question.accepted,+1)]))。|offset|length|style|CODE|922257|func.sum(sqlalchemy.cast(Question.accepted,+sqlalchemy.Integer))|922258|最后，如果您希望将求和作为Python端的整数，则胁迫()可以完成以下任务：|922259|sqlalchemy.type_coerce(func.sum(Question.accepted),+sqlalchemy.Integer)|922260|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.cast|1|https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.type_coerce^0|0|0|1Q|16|10|B|0|0|0|P|4|1|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@$I|15|J|16|K|L]]|9|@$I|17|J|18|1|19]]|A|$]]|$1|M|3|N|5|D|7|1A|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1B|8|@]|9|@$I|1C|J|1D|1|1E]]|A|$]]|$1|Q|3|R|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]|10|$5|V|W|X|A|$Y|11]]]]

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:

<pre class="lang-py prettyprint-override"><code>func.sum(Question.accepted)
</code></pre>

If your SQL engine returns a SUM of booleans as a boolean (not sure there is any), you can use <a href="https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.cast" rel="nofollow noreferrer">expression function cast()</a> to force it into an integer instead of using <code>func.count(case([(Question.accepted, 1)]))</code>:

<pre class="lang-py prettyprint-override"><code>func.sum(sqlalchemy.cast(Question.accepted, sqlalchemy.Integer))
</code></pre>

Finally, if you want the sum as an integer on the Python side the <a href="https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.type_coerce" rel="nofollow noreferrer">expression function type_coerce()</a> does the trick:


<pre><code>sqlalchemy.type_coerce(func.sum(Question.accepted), sqlalchemy.Integer)
</code></pre>

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

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

<pre><code>.having(func.count(Question.accepted) &gt;
 func.count(not_(Question.accepted)))
</code></pre>

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

I tried something like this

<pre><code>.having(func.count(func.if_(Question.accepted, 1, 0)) &gt;
 func.count(func.if_(Question.accepted, 0, 1)))
</code></pre>

But I get an error

<blockquote>
 function if(boolean, integer, integer) does not exist
</blockquote>

(seems it doesn't exist in postgresql).

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

SQLAlchemy func.count on boolean column

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如何简单地计算特定列为true的行数和false的行数？我不能(或者我可以吗?)使用count()运行查询，因为我将此计数嵌入在having()子句中，如下所示：.having(func.count(Question.accepted) >        func.count(not_(Question.accepte...

问布尔列上的SQLAlchemy func.count
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问布尔列上的SQLAlchemy func.countEN