How to get a COUNT() value when GROUP BY has no row?

RDMS : MariaDB 10.1.23

MCRE :

create table entity
(
    id_entity int auto_increment,
    created_at timestamp default CURRENT_TIMESTAMP,
    constraint product_log_pk
        primary key (id_entity)
);

INSERT INTO entity SET created_at = CURRENT_TIMESTAMP;
INSERT INTO entity SET created_at = CURRENT_TIMESTAMP;
INSERT INTO entity SET created_at = CURRENT_TIMESTAMP;

-- I would like this to return 0
SELECT IF(COUNT(e.id_entity) = 0, 0, e.id_entity) 
FROM entity e
WHERE e.created_at < CURRENT_DATE - INTERVAL 1 DAY GROUP BY e.created_at;

SQLFiddle: http://sqlfiddle.com/#!9/f32a01/1

---

Consider the following query :

SELECT IF(COUNT(e.some_field) = 0, 0, e.some_field) 
FROM entity e
WHERE e.created_at > CURRENT_DATE - INTERVAL 1 DAY GROUP BY e.created_at

Without the GROUP BY clause, I know that COUNT() will return 0 if no row match the query.

I also know that, if my GROUP BY “fails” (If i have no row to group by, in my example, if no row satisfy e.created_at > CURRENT_DATE - INTERVAL 1 DAY) then, COUNT() will not return anything (0 row). How can I make sure COUNT() returns 0 (1 row with the value 0) in that case ?

Answer

Grouping is done after where clause is evaluated; if the where clause eliminates all rows then there is nothing to group.

I am guessing that you want conditional aggregation. Here is an example:

name | dept | gender
-----+------+-------
jane | acc  | f
john | acc  | m
jack | hr   | m
jake | hr   | m

And query to count all female employees by department:

SELECT dept, COUNT(CASE WHEN gender = 'f' THEN 1 END)
FROM employee
-- WHERE gender = ... will/could eliminate entire group(s)
GROUP BY dept

dept | count
-----+------
acc  | 1
hr   | 0