Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/#!/StackDBAs/status/503584265909846016
Added Future Table
Source Link
Jon H
Jon H
  • 143
  • 6

EDIT: Future Table

+-----------------------------------------------------------------------------------------------+
| TypeOfLottery | MinRegNum | MaxRegNum | NumOfRegPicks | MinSpeNum | MaxSpeNum | NumOfSpePicks |
+-----------------------------------------------------------------------------------------------+
| Pick3         | 0         | 9         | 3             | 0         | 0         | 0             |
| Pick4         | 0         | 9         | 4             | 0         | 0         | 0             |
| Powerball     | 1         | 51        | 6             | 1         | 45        | 1             |
+-----------------------------------------------------------------------------------------------+

Now That I think about it, I dont see any reason to have this table. If I just created a new table for each type of lotto that should work. If I wanted to go further the above table would give me the "Rules" for each lotto.

EDIT: Future Table

+-----------------------------------------------------------------------------------------------+
| TypeOfLottery | MinRegNum | MaxRegNum | NumOfRegPicks | MinSpeNum | MaxSpeNum | NumOfSpePicks |
+-----------------------------------------------------------------------------------------------+
| Pick3         | 0         | 9         | 3             | 0         | 0         | 0             |
| Pick4         | 0         | 9         | 4             | 0         | 0         | 0             |
| Powerball     | 1         | 51        | 6             | 1         | 45        | 1             |
+-----------------------------------------------------------------------------------------------+

Now That I think about it, I dont see any reason to have this table. If I just created a new table for each type of lotto that should work. If I wanted to go further the above table would give me the "Rules" for each lotto.

Source Link
Jon H
Jon H
  • 143
  • 6

Bad Design or Complicated Query?

Problem: For fun I am building a Database for lottery results to get some statistics off of. As of now I am only interested in a Pick 3 style lotto and might introduce more later. How would you approach a query to produce the desired result below? Would there be a better table design?

Data

+----------------------------------------------+
| Date     | Time | NumOne | NumTwo | NumThree |
+----------------------------------------------+
| 01/02/14 | M    | 8      | 2      | 1        |
| 01/02/14 | E    | 8      | 8      | 8        |
| 01/03/14 | M    | 1      | 9      | 4        |
| 01/04/14 | E    | 0      | 7      | 5        |
+----------------------------------------------+

Desired Result

+----------------+
| Number | Count |
+----------------+
| 0      | 1     |
| 1      | 2     |
| 2      | 1     |
| 3      | 0     |
| 4      | 1     |
| 5      | 1     |
| 6      | 0     |
| 7      | 1     |
| 8      | 4     |
| 9      | 1     |
+----------------+