英文:
counting booleans in 2 different columns of sql (derby) database
问题
以下是您要翻译的内容：
我是Java和SQL的新手，对一个混淆了我的数据的查询感到困惑。我有一个包含3列的简单表格（IDI（整数），column1（布尔），column2（布尔））。
我已经想出了可以成功运行的查询，但数据会在这两个布尔列之间复制。如果我运行select * from booleans，所有的数据都是正确的，但是当我编写一个查询来计算每一列的数量（总数）时，两列的总数却相同？
当我运行下面的查询时，我得到了如图所示的正确结果：
select * from booleans order by idi
然而，当我尝试计算和分组时，两列的数据都相同。每一列应该有一个数字，另一列为0。以下是代码和结果图片：
select idi, count(column1) as "Column 1", count(column2) as "Column2"
from booleans
group by idi
我将不胜感激，如果有任何关于为什么在聚合表中显示不正确的理论。
英文:
I am new to Java and SQL and am stumped by a query that is mixing up my data.
I have a simple table with 3 columns (IDI (integer), column1 (bool), column2 (bool)).
I have come up with queries that will succeed but with data being copied between the 2 boolean columns. If I do select * from booleans all the data is correct but when I write a query to count (total) each column the totals come out the same?
When I run this query I get correct results as shown in image;
select * from booleans order by idi
However, when I try to count and group both columns have the same data. Each column should have a number in one column and a 0 in the other. Here is the code and picture of the results:
select idi, count(column1) as "Column 1", count(column2) as "Column2"
from booleans
group by idi
I would appreciate any theories as to why this is displaying incorrectly in the aggregated table.
答案1
得分: 2
为了仅计算 true 值，排除 false 值，您可以执行以下操作：
select idi,
sum(case when column1 then 1 else 0 end) as count1,
sum(case when column2 then 1 else 0 end) as count2
from booleans
group by idi
详细说明：count(*) 函数计算有多少个 非空值。对于此函数，值是 true 还是 false 并不重要。
英文:
In order to count only true values, excluding the false ones you can do:
select idi,
sum(case when column1 then 1 else 0 end) as count1,
sum(case when column2 then 1 else 0 end) as count2
from booleans
group by idi
To elaborate: count(*) counts how many non-null values are there. For this function, it doesn't matter if the values are true or false.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论