0

Possible Duplicate:
Generating a series of dates

What is the best way in mysql to generate a series of numbers in a given range?

The application I have in mind is to write a report query that returns a row for every number, regardless of whether there is any data to report. An example in its simplest form might be:

SELECT numbers.num, COUNT(answers.id)
FROM <series of numbers between X and Y> numbers
     LEFT JOIN answers ON answers.selection_number = numbers.num
GROUP BY 1

I have tried creating a table with lots of numbers, but that seems like a poor workaround.

Community
  • 1
  • 1
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    unclear exactly what you want. will a sequence work? – Randy Dec 31 '12 at 20:27
  • Seems like the same as How do I get a list of numbers in MySQL? to which I got the answer that there's no good way to do this. – prosfilaes Dec 31 '12 at 20:31
  • "Where did you get this cool hat?" The answer is hatwhoring. – PeeHaa Dec 31 '12 at 20:33
  • @Bohemian :) I saw one with generating dates earlier... So if you are posting again here on similar lines, then there has to be something more interesting underneath. – bonCodigo Dec 31 '12 at 20:39
  • @JanDvorak Visit this chat room I set up for this purpose: http://chat.stackexchange.com/rooms/6904/stackoverflow-new-years-eve-hat right now and I'll fix you up – Bohemian Dec 31 '12 at 20:55
  • @bonCodigo I believe the term being used is "hat whoring" :) – Bohemian Dec 31 '12 at 23:40

2 Answers2

2

First, create a table called ints which will contain one record for each digit from 0 to 9.

CREATE TABLE ints ( i tinyint );

Then populate that table with data.

INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Now you can use a query such as the following to generate a sequence of numbers.

SELECT generator.num, COUNT(answers.id)
FROM (
    SELECT a.i*10 + b.i AS num
    FROM ints a, ints b
    ORDER BY 1
) generator
LEFT JOIN answers ON answers.selection_number = generator.num
WHERE generator.num BETWEEN 18 AND 43

To add another place value to the generated numbers, just add more joins of the ints table and adjust the calculations accordingly. The following will generate three-digit numbers:

SELECT generator.num, COUNT(answers.id)
FROM (
    SELECT a.i*100 + b.i*10 + c.i AS num
    FROM ints a, ints b, ints c
    ORDER BY 1
) generator
LEFT JOIN answers ON answers.selection_number = generator.num
WHERE generator.num BETWEEN 328 AND 643
Brian Showalter
  • 4,321
  • 2
  • 26
  • 29
0

you can try with group by numbers.num instead of group by 1

SELECT numbers.num, COUNT(answers.id)
FROM numbers
     LEFT JOIN answers ON answers.selection_number = numbers.num
WHERE numbers.num between X and Y
GROUP BY numbers.num
Rashedul.Rubel
  • 3,446
  • 25
  • 36