Most used Numbers in Lottery Database

Question

I have an Access 2000 database that contains a table of little lotto numbers. The first column is "Drawdate", the second "P1", the third "P2", the fourth "P3", the fifth "P4", and the sixth "P5". I'm trying to find 10 numbers from the table that are used the most together.

10 numbers used 5 at a time would produce 252 combinations. From the table of winning numbers I want to find 10 numbers most used in groups of 5.

Answer 1

You need to count how many times each number is used.

But you do not know which column a number could be in.

Therefore your could first count how many times the number 1 appears in all columns, then the number 2 and so on.

SELECT Count(*) WHERE P1=1

Answer 2

Personally, I'd build a second table from the first. Something like...

INSERT INTO tblNumbers(Number,NumCount) SELECT P1, count(P1) from tblDraws group by P1 INSERT INTO tblNumbers(Number,NumCount) SELECT P2, count(P2) from tblDraws group by P2 INSERT INTO tblNumbers(Number,NumCount) SELECT P3, count(P3) from tblDraws group by P3 etc

then at the end, just execute

SELECT Number Number, SUM(NumCount) from tblNumbers group by Number

Answer 3

I would unpivot the data first so you have:

DrawDate, Position, Number

Where Position in (1, 2, 3, 4, 5)

Since you don't care really about position, it's easy enough to exclude it from queries now (or drop the column altogether).

The portable UNPIVOT is:

SELECT DrawDate, 1 AS Position, P1 AS [Number] FROM tbl
UNION ALL
SELECT DrawDate, 2 AS Position, P2 AS [Number] FROM tbl
UNION ALL
SELECT DrawDate, 3 AS Position, P3 AS [Number] FROM tbl
UNION ALL
SELECT DrawDate, 4 AS Position, P4 AS [Number] FROM tbl
UNION ALL
SELECT DrawDate, 5 AS Position, P5 AS [Number] FROM tbl

This can actually be embedded to find a most used number result without remodelling your data (I'm not even bothering with the DrawDate and Position):

SELECT TOP 1 [Number], COUNT(*)
FROM (
    SELECT /* DrawDate, 1 AS Position, */ P1 AS [Number] FROM tbl
    UNION ALL
    SELECT /* DrawDate, 2 AS Position, */ P2 AS [Number] FROM tbl
    UNION ALL
    SELECT /* DrawDate, 3 AS Position, */ P3 AS [Number] FROM tbl
    UNION ALL
    SELECT /* DrawDate, 4 AS Position, */ P4 AS [Number] FROM tbl
    UNION ALL
    SELECT /* DrawDate, 5 AS Position, */ P5 AS [Number] FROM tbl
) AS unpivoted
GROUP BY [Number]
ORDER BY COUNT(*) DESC

If you define what you mean by numbers used together...