Count numbers drawn on a lottery table

I am new to comics studies and I have a table with the results of lotofácil where I would like to make a count that looks for, for example, how many times the ten 20 was drawn. I've done several tests but I only got column to column and if I put a general count it points to values considering the dates and goes wrong. Below is a part of the image of my bank.

Draw

Author: Bacco, 2019-06-18
Source

2 answers

This answer ignores the fact that the value 20 can not be repeated since it is one of the business rules this value can not be repeated in several columns of the same draw.

SET @valor = 20;
select 
      (select count(Bola1) from sorteiosanteriores where Bola1 = @valor) 
    + (select count(Bola2) from sorteiosanteriores where Bola2 = @valor)
    + (select count(Bola3) from sorteiosanteriores where Bola3 = @valor)
    + (select count(Bola4) from sorteiosanteriores where Bola4 = @valor)
    + (select count(Bola5) from sorteiosanteriores where Bola5 = @valor)
    + (select count(Bola6) from sorteiosanteriores where Bola6 = @valor)
    + (select count(Bola7) from sorteiosanteriores where Bola7 = @valor)
    + (select count(Bola8) from sorteiosanteriores where Bola8 = @valor)
    + (select count(Bola9) from sorteiosanteriores where Bola9 = @valor)
    + (select count(Bola10) from sorteiosanteriores where Bola10 = @valor)
    + (select count(Bola11) from sorteiosanteriores where Bola11 = @valor)
    + (select count(Bola12) from sorteiosanteriores where Bola12 = @valor)
    + (select count(Bola13) from sorteiosanteriores where Bola13 = @valor)
    + (select count(Bola14) from sorteiosanteriores where Bola14 = @valor)
    + (select count(Bola15) from sorteiosanteriores where Bola15 = @valor);

What this does is count in all columns the value placed in the variable @value and at the end returns the value of the sum of the counts.

For an answer based on the business rule see the answer of @Bacco.

 4
Author: MauroAlmeida, 2019-06-18 21:55:14

Since this is a draw where there is no repetition of values in the columns, you can use the operator IN: 

SELECT
      COUNT(*)
FROM
      sorteiosanteriores
WHERE
      20 IN (bola1,bola2,bola3,bola4,bola5,bola6 ... bola15)

See working on SQL FIDDLE.

Now you need to see if this "horizontal" modeling is the best solution. Probably a related table is better than a bunch of columns as same purpose.

Attention: this query has been optimized for the "easy lot" case, and counts the number of rows that 20 is returned, regardless of whether it occurred once or several times in the same row. For a general Count, see @MauroAlmeida's answer or the following excerpt.

Counting multiple occurrences 

SELECT
      SUM((bola1=20)+(bola2=20)+(bola3=20)+ ... +(bola15=20))
      AS quantidade
FROM
     sorteiosanteriores

See working on SQL FIDDLE.

This works in a very simple way. The true in MySQL has the value 1, and the false 0. Thus, just add the amount of equals per row, and aggregate with SUM.

This second option counts the amount total number occurrences, including repetitions on the same line.

 6
Author: Bacco, 2019-06-18 22:23:03