mysqlgroup-bybooleantinyint

Is there a way to make an AND operation over a column of TINYINT(1) in MYSQL?

I got the following table and I need to return 1 if all rows have disponibilidad = 1

EXAMPLE TABLE

The following QUERY works just fine, but i was looking for a more efficient way of doing it.

QUERY:

SELECT IF(AVG(disponibilidad) < 1, 0, 1) AS newResult
FROM pasteleria.compone
RIGHT JOIN pasteleria.ingredientes
    ON pasteleria.compone.id_ingrediente = pasteleria.ingredientes.id_ingrediente
WHERE id_componente = 1;

RESULT:

RESULT

As I see it, with an 'AND' it would be far more efficient, since it wouldn't have to do AVG().

Solution

  • MySql does not support a boolean AND aggregate function like Postgresql's bool_and.

    Why not a simple MIN():

    SELECT MIN(disponibilidad) AS newResult
FROM pasteleria.compone
RIGHT JOIN pasteleria.ingredientes
    ON pasteleria.compone.id_ingrediente = pasteleria.ingredientes.id_ingrediente
WHERE id_componente = 1;

    This will return 1 only if all values of the column are 1 (provided the column is not nullable) and 0 if there is at least one row with 0.