count(case when col1='A' then 1 else null end)

[PAscal]

Hello,

I'm testing release 1.0.23 with jar file found in Jenkins.
It seems that:

SELECT
tab.COL1,
count(case when tab.COL1 ='AA' then 1 else null end)
FROM
tab
group by
tab.COL1

returns non Zero values when col1 != 'AA'
could you verify if I'm right ?

note that
SELECT
tab.COL1,
max(case when tab.COL1 ='AA' then 1 else null end)
FROM
tab
group by
tab.COL1

is OK

Regards
PAscal

[Simon Chenery]

This is logged as CsvJdbc bug #111, COUNT with GROUP BY incorrectly counts NULL values

[Aashish]

Hello, using release 1.0.23,
we have a csv file that has let's say 3 columns
col1,col2,col3
A,123,abc
A,456,def
,789,ghi
B,135,adg

We are trying to get a count of null values from the file. I have tried following but it always gives 0

SELECT
SUM(CASE WHEN col1 IS NULL THEN 1 ELSE 0 END) as count_blanks
FROM csv1

I have tried
COUNT((CASE WHEN CHANGEME is NULL THEN 1 ELSE NULL END)) gives 0

The expected answer is 1 (3rd record with missing col1)
What is the right syntax to get null count of a field.

Thank you.

[Simon Chenery]

CsvJdbc returns an empty string for empty columns.

Maybe your SQL should be:

SELECT
SUM(CASE WHEN col1 = '' THEN 1 ELSE 0 END) as count_blanks
FROM csv1