Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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.

share|improve this question

1 Answer 1

up vote 10 down vote accepted

Saving your data differently would make that query trivial. Namely: one number per row. Something like:

results(Date, Time, Rank, Number)

where Rank would be 1 (or zero) for the first number, 2 for the second, etc. (Only if the order has importance, drop the rank if it doesn't.)

Then your query boils down to (ignoring reserved identifiers):

select number, count(*)
from results
-- where date = ...
group by number
order by number;

With your current schema, you can still get it fairly easily with a union:

select number, count(*)
from (
  select NumberOne as number from Results
  union all
  select NumberTwo as number from Results
  union all
  select NumberThree as number from Results
) AllNumbers
group by number
order by number;

(Or use a with AllNumbers as (...) CTE.)


When it comes to extending your application to more than three numbers, it's pretty clear which solution wins - you really don't want to add more columns for every type of draw, or produce a huge list of unions.

There's one issue I see though: a lot of data duplication for each row. More so if you need to add a column with the "type" of draw (to distinguish between "pick 3", pick 4" or whatever types of game you want).

You could mitigate that by only storing a "draw id", (optional) rank and number, and have another table where you store the "metadata" for each draw id. There probably are intermediate schemes that would fit your application better - depends on what trade-offs you can/want to make.

share|improve this answer
    
You will almost certainly want to seperate the complete draw from the individual items in the draw. Consider what happens if you want to store the winners from the draws. –  Taemyr Aug 20 at 13:14
    
Awesome! I never even thought of the Rank idea, so clever! Please see my update added to the Original Question. –  Jon H Aug 20 at 15:03
    
@JonH: "one table per [foo]" is usually a bad idea. What do you gain by having separate tables to store the results of each type of game (for all the games of the same kind of course)? –  Mat Aug 20 at 16:28
    
@Mat: I feel that by having an individual table for each game would result in less data duplication vs a single table with multiple games. I am not an expert (and don't want this taken in the wrong context) and will take any advice that is handed to me. –  Jon H Aug 20 at 23:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.