I was solving some SQL tasks and got confused with the following thing:
CREATE TABLE data (
len INT
);
INSERT INTO data (len)
VALUES
(NULL),
(100),
(200),
(300),
(400);
suppose we’ve created such a table and we wanna count all values that are less than 200. I am using this online compiler to do it.
my first attempt gives 1 as expected
SELECT COUNT(*) FILTER (WHERE len < 200)
FROM data
then I thought whether I can simplify it and just do
SELECT COUNT(len < 200) FROM data
but it gives 4 as an answer.
So my question is: can we use logic expressions inside of aggregate functions? If the answer is NO, why?