You explain:
I get result 1453
only when all items in column attributeY
are empty for 1453
.
But that's incorrect. Bold emphasis mine. The aggregate function count
returns (per documentation):
number of input rows for which the value of expression is not null
The same is true for SQLite (per documentation):
The count(X) function returns a count of the number of times that X is
not NULL in a group.
You obviously have one or more rows with a non-null value in attributeY
in your Postgres table - probably an empty string ''
.
Test with:
SELECT *
FROM myschema.table
WHERE attributeX = 1453
AND attributeY IS NOT NULL;
Be sure to understand the difference between "empty" (''
) and NULL
:
Empty strings are character types (typically text
, varchar
or char
) with 0 characters (''
) - so basically "nothing", the equivalent of 0
for a numeric data type. NULL
is possible for any data type and means "unknown". Some clients have a hard time making the difference clear in their display.
Alternative query
To find attributeX
where all attributeY
are either empty or NULL use this alternative query:
SELECT attributeX
FROM myschema.table
GROUP BY attributeX
HAVING NOT bool_or(attributeY <> '');
The expression attributeY <> ''
is only true
for non-null, non-empty attributeY
. The aggregate function bool_or
returns (per documentation):
true if at least one input value is true, otherwise false
Those are the results we exclude (NOT ...
) and return the rest. Voilá.
There are many other (less elegant) ways to achieve the same. Like:
... HAVING count(CASE WHEN attributeY <> '' THEN 1 END) = 0;
... HAVING count(attributeY <> '' OR NULL) = 0;
... HAVING count(NULLIF(attributeY, '') = 0;
More:
Best Answer
It's quite simple using GROUP_CONCAT():
By the way,
row
andtext
are bad names for columns, as they are reserved keywords.