Combining column value with Mysql

Question

I am working on a report in Mysql where I need to show a percentage of several events that occurred with a group of entities.

The structure of my table is as follows:

| id | eventoA | eventoB | eventoC | eventoD |
|----|---------|---------|---------|---------|
| 1  | 0       | 0       | 0       | 0       |
| 2  | 1       | 0       | 0       | 0       |
| 3  | 1       | 0       | 0       | 0       |
| 4  | 0       | 0       | 1       | 0       |
| 5  | 0       | 1       | 0       | 0       |
| 6  | 1       | 1       | 0       | 0       |
| 7  | 1       | 1       | 0       | 0       |
| 8  | 1       | 0       | 1       | 0       |
| 9  | 0       | 0       | 1       | 0       |
| 10 | 0       | 0       | 0       | 0       |

The columns EventoA, EventoB and so on are like BIT and are updated by the application when a given event is triggered to that entity. Today I can generate that report with the following query:

SELECT COUNT(`id`) AS `Total`,
  SUM(`eventoA`) AS `eventoDisparado`,
  COUNT(`id`) - SUM(`eventoA`) AS `eventoNaoDisparado`
FROM tabela;

Query result:

Total: 10, event Available: 5, event Available: 5

But this way I can not combine multiple events because the number of events ends up not hitting:

SELECT COUNT(`id`) AS `Total`,
  SUM(`eventoA`) + SUM(`eventoB`) AS `eventoDisparado`,
  COUNT(`id`) - SUM(`eventoA`) + SUM(`eventoB`) AS `eventoNaoDisparado`
FROM tabela;

Expected result:

Total: 10, event Available: 6, event Available: 4

Result obtained

Total: 10, event Available: 8, event Available: 2

I’d like to sort of combine the columns eventoA and eventoB thus:

| id | eventoA | eventoB | eventoA + eventoB |
|----|---------|---------|-------------------|
| 1  | 0       | 0       | 0                 |
| 2  | 1       | 0       | 1                 |
| 3  | 1       | 0       | 1                 |
| 4  | 0       | 0       | 0                 |
| 5  | 0       | 1       | 1                 |
| 6  | 1       | 1       | 1                 |
| 7  | 1       | 1       | 1                 |
| 8  | 1       | 0       | 1                 |
| 9  | 0       | 0       | 0                 |
| 10 | 0       | 0       | 0                 |

You may get the expected result using a OR bitwise.

It is possible to use such operators directly in a query? What other alternatives I have to achieve the expected result?

Answer 1

I ran a test on SQL Server (for Mysql to see the end of the answer) with two versions, one with the type fields int and one with the guy bit. The version of the type int worked normally. I’ll leave it here while I check the version of the type bit

Version with int type fields:

create table teste (
  id int,
  eventoA int,
  eventoB int,
  eventoC int
);

insert into teste (id,eventoA,eventoB,eventoC) values (1,0,0,0);
insert into teste (id,eventoA,eventoB,eventoC) values (2,1,0,1);
insert into teste (id,eventoA,eventoB,eventoC) values (3,1,0,1);
insert into teste (id,eventoA,eventoB,eventoC) values (4,0,0,0);
insert into teste (id,eventoA,eventoB,eventoC) values (5,0,1,1);
insert into teste (id,eventoA,eventoB,eventoC) values (6,1,1,1);
insert into teste (id,eventoA,eventoB,eventoC) values (7,1,1,1);
insert into teste (id,eventoA,eventoB,eventoC) values (8,1,0,1);
insert into teste (id,eventoA,eventoB,eventoC) values (9,0,0,0);
insert into teste (id,eventoA,eventoB,eventoC) values (10,0,0,0);

SELECT 
  COUNT(*) TOTAL,
  SUM(CASE WHEN EVENTO = 0 THEN 1 ELSE 0 END) EVENTO_NAO_DISPARADO,
  SUM(CASE WHEN EVENTO = 1 THEN 1 ELSE 0 END) EVENTO_DISPARADO
FROM  
(
    SELECT
        ID,
        CASE WHEN SUM(eventoA + eventoB + eventoC) >= 1 THEN 1 ELSE 0 END EVENTO
    FROM TESTE
    GROUP BY ID
) A

UPDATE - Version with bit type:

create table teste (
  id int,
  eventoA bit,
  eventoB bit,
  eventoC bit
)

insert into teste (id,eventoA,eventoB,eventoC) values (1,0,0,0)
insert into teste (id,eventoA,eventoB,eventoC) values (2,1,0,1)
insert into teste (id,eventoA,eventoB,eventoC) values (3,1,0,1)
insert into teste (id,eventoA,eventoB,eventoC) values (4,0,0,0)
insert into teste (id,eventoA,eventoB,eventoC) values (5,0,1,1)
insert into teste (id,eventoA,eventoB,eventoC) values (6,1,1,1)
insert into teste (id,eventoA,eventoB,eventoC) values (7,1,1,1)
insert into teste (id,eventoA,eventoB,eventoC) values (8,1,0,1)
insert into teste (id,eventoA,eventoB,eventoC) values (9,0,0,0)
insert into teste (id,eventoA,eventoB,eventoC) values (10,0,0,0)

SELECT 
  COUNT(*) TOTAL,
  SUM(CASE WHEN EVENTO = 0 THEN 1 ELSE 0 END) EVENTO_NAO_DISPARADO,
  SUM(CASE WHEN EVENTO = 1 THEN 1 ELSE 0 END) EVENTO_DISPARADO
FROM  
(
    SELECT
        ID,
        eventoA | eventoB | eventoC EVENTO
    FROM TESTE
) A

UPDATE 2 - Fiddle Mysql: http://sqlfiddle.com/#! 9/20011/1/0

Note: There was no change to the version with the type bit SQL Server. The only thing needed was to include ";" at the end of Create Table and each Insert.

Answer 2

I was curious, I tried using Binary operators and it worked.

select count(entidade) as Total,
SUM(evento_a | evento_b) as EventoDisparado
from evento;

Return: Total: 10, Eventsigned: 6

Reference: Mysql documentation