Combining column value with MySQL

Navigation
5

I'm working on a report in MySQL where I need to show a percentage of various 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 of type BIT and are updated by the application when a certain event is triggered for that entity. Today I can generate this 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, eventDisplayed: 5, eventNotDisplayed: 5

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

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, eventDisplayed: 6, eventNotDisplayed: 4

Result obtained

  

Total: 10, eventDisplayed: 8, eventNotDisplayed: 2

I would like to combine the columns eventoA and eventoB like this: 

| 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 .

Is it possible to use this type of operators directly in a query ? What other alternatives do I have to get the expected result?

    
asked by anonymous 06.08.2015 / 18:51

2 answers

2

I was curious, I tried to use Binary operators and it worked. 

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

Return: Total: 10, EventStream: 6

Reference: MySQL Documentation

    
06.08.2015 / 19:54
2

I made a test in SQL Server (for MySQL see the end of the answer) with two versions, one with the fields of type int and one with the type bit . The version of type int worked normally. I'll leave it here while I check the bit version

Version with the fields of type int: 

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: link

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

    
06.08.2015 / 19:53
Adjust bar code size Barbecue How can I get the maximum and minimum values after applying LOESS