Q:

counting booleans in 2 different columns of sql (derby) database

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.

A:

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.

