Counter Occurrences of Different Numbers in Different Columns

Question

First of all, sorry if it is a duplicated question...

I have an Android App (SQLITE) which check Lotery games. Basically, this lottery draws 5 numbers among 40 and I save them as follows:

|---------------------------------------------------------------------------|
|                               TABLE: lotery                               |
|---------------------------------------------------------------------------|
|   game_number |   NUM1    |   NUM2    |   NUM3    |   NUM4    |   NUM5    |
|---------------------------------------------------------------------------|
|       1       |    02     |    32     |    33     |    34     |    40     |
|       2       |    02     |    14     |    16     |    39     |    40     |
|       3       |    01     |    11     |    12     |    25     |    27     |
|       4       |    02     |    22     |    23     |    30     |    36     |
|                                   ......                                  |
|---------------------------------------------------------------------------|

Now, I would like to consult how frequent a number is selected.

Example:

• Number 01 was drawn 1 time (in game_number==3)
• Number 02 was drawn 3 times (in game_number==1, game_number==2 and game_number==4))
• Etc...

What I have today:

SELECT * FROM
        (select count(*) FROM lotery where 1 IN(NUM1,NUM2,NUM3,NUM4,NUM5)),
        (select count(*) FROM lotery where 2 IN(NUM1,NUM2,NUM3,NUM4,NUM5)),
        (select count(*) FROM lotery where 3 IN(NUM1,NUM2,NUM3,NUM4,NUM5)),
        ....
        (select count(*) FROM lotery where 40 IN(NUM1,NUM2,NUM3,NUM4,NUM5));

This will produce:

|-----------------------------------------------------------|
|  count(*) |  count(*) |  count(*) |  ......   |  count(*) |
|-----------------------------------------------------------|
|     1     |     3     |     0     |  ......   |     2     |
|-----------------------------------------------------------|

First column correspond to "01". Second Column correspond to number "02". Etc... until 40th Column

Finally the Questions

Current implementation has low performance in some old and low cost devices.

So, I have following questions:

1. Do you see a better query for current table implementation?

2. Is it possible to query and get the results ordered?

Today, I perform that query and I manually sort the results in DESC order. My activity show the results in a listview in DESC order.

Since I'm using a list view, I don't need all results at once. For example, I could query only first 10 more frequent numbers (to fill the screen) and only consult remain numbers if the user scroll the list view.

Anyway, if you see any possible improvement in current implementation, I will appreciate...

Thanks a lot in advance!!

Answer 1

1. Do you see a better query for current table implementation?

Yes, check below :)

2.Is it possible to query and get the results ordered?

Yes.

---

You could use tally table and LEFT JOIN:

WITH num(n) AS
(
   VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
         ,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
         ,(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
         ,(31),(32),(33),(34),(35),(36),(37),(38),(39),(40)
)
SELECT num.n, COUNT(l.game_number) AS cnt
FROM num 
LEFT JOIN lottery l
  ON num.n IN (l.NUM1, l.NUM2,l.NUM3, l.NUM4,l.NUM5)
GROUP BY num.n
ORDER BY cnt DESC, num.n;

SqlFiddleDemo

Output:

╔═════╦═════╗
║ n   ║ cnt ║
╠═════╬═════╣
║  2  ║   3 ║
║ 40  ║   2 ║
║  1  ║   1 ║
║ 11  ║   1 ║
║ 12  ║   1 ║
║ 14  ║   1 ║
║ 16  ║   1 ║
║ 22  ║   1 ║
║ 23  ║   1 ║
║ 25  ║   1 ║
║ 27  ║   1 ║
║ 30  ║   1 ║
║ 32  ║   1 ║
║ 33  ║   1 ║
║ 34  ║   1 ║
║ 36  ║   1 ║
║ 39  ║   1 ║
║  3  ║   0 ║
║  4  ║   0 ║
║  5  ║   0 ║
║  6  ║   0 ║
║  7  ║   0 ║
║  8  ║   0 ║
║  9  ║   0 ║
║ 10  ║   0 ║
║ 13  ║   0 ║
║ 15  ║   0 ║
║ 17  ║   0 ║
║ 18  ║   0 ║
║ 19  ║   0 ║
║ 20  ║   0 ║
║ 21  ║   0 ║
║ 24  ║   0 ║
║ 26  ║   0 ║
║ 28  ║   0 ║
║ 29  ║   0 ║
║ 31  ║   0 ║
║ 35  ║   0 ║
║ 37  ║   0 ║
║ 38  ║   0 ║
╚═════╩═════╝

EDIT:

it seems that SQLITE3 embed in Android API16 is v3.7.11 Do you see any limitation by using you answer?

If WITH is not supported you could easily change it with subquery:

SELECT num.n, COUNT(l.game_number) AS cnt
FROM (SELECT 1 AS n
      UNION SELECT 2
      UNION SELECT 3
       -- ... up to 40
      ) num
LEFT JOIN lottery l
  ON num.n IN (l.NUM1, l.NUM2,l.NUM3, l.NUM4,l.NUM5)
GROUP BY num.n
ORDER BY cnt DESC, num.n;

SqlFiddleDemo2