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

Question

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

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:

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

What's the point of `AVG` without group by ? https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_avg — Ergest Basha, Dec 21 '21 at 18:49
It's just an example query that works at the moment, if the AVG of values is lower than 1 it means that at least 1 of them is 0. — felipe soto, Dec 21 '21 at 18:58

forpas · Accepted Answer · 2021-12-21T20:06:07.357

1

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.

edited Dec 21 '21 at 20:06

answered Dec 21 '21 at 19:15

forpas

160,666
10
38
76

Nice one, better than what i had but still not the answer i was looking for – felipe soto Dec 21 '21 at 19:24
@felipesoto MySql does not support an aggregate AND function. This is why I posted MIN as a solution. – forpas Dec 21 '21 at 19:25
I understand, add that to your answer to accept it as solution. Thanks! – felipe soto Dec 21 '21 at 19:50

score 0 · Answer 2 · answered Dec 21 '21 at 19:08

0

How about something like

SELECT IF(COUNT(*)>0,0,1) AS newResult
FROM pasteleria.compone
RIGHT JOIN pasteleria.ingredientes
    ON pasteleria.compone.id_ingrediente = pasteleria.ingredientes.id_ingrediente
WHERE id_componente = 1
AND disponibilidad <> 1

so that if there are any rows where disponibilidad is not 1, you output 0, otherwise if it's zero (so all disponibilidad values are 1) you output 1?

answered Dec 21 '21 at 19:08

ivarch

56
3

It's pretty much the same solution as with AVG() – felipe soto Dec 21 '21 at 19:13

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

2 Answers2