MySQL: is it possible to "populate" a SELECT value without a table?

Question

I need to display the total number of orders for each year and month. But for several months there is no data, but I want to show this month (with a full zero value). I could create ancillary “months” with 12 entries for each year, but could there be a way to get a whole series of months without introducing a new table?

Something like:

SELECT [all year-month combinations between january 2000 and march 2011] FROM DUAL AS years_months 

Does anyone have any ideas how to do this? Can you use SELECT with some formula to “create” data on the fly ?!

UPDATE:

Found it myself: generate days from a date range

The accepted answer in this question is what I am looking for. This may not be the easiest way, but it does what I want: fill in the selection with data based on the formula ....

To "create" a table "on the fly" with all the months of the last 10 years:

 SELECT CONCAT(MONTHNAME(datetime), ' ' , YEAR(datetime)) AS YearMonth, MONTH(datetime) AS Month, YEAR(datetime) AS Year FROM ( select (curdate() - INTERVAL (aa + (10 * ba) + (100 * ca)) MONTH) as datetime from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c LIMIT 120 ) AS t ORDER BY datetime ASC 

I have to admit that this is VERY exotic, but it works ...

I can use this select to connect it to my "orders" table and get the totals for each month, even if there is no data for a specific month.

But using a "numbers" or "calendar" table is probably the best option, so I'm going to use this.

Answer 1

If at all possible, try to stay away from data generation on the fly. It makes very simple queries ridiculously complex, but above all: it confuses the optimizer to the end.

If you need a series of integers, use a static table of integers. If you need a series of dates, months, or something else, use the calendar table. If you are not dealing with some really extraordinary requirements, a static table is the way to go.

I gave an example of how to create a table of numbers and a minimum calendar table (dates only) in this answer .

If you have these tables in place, it will be easy for you to resolve your request.

• Set of order data for MONTH.
• Correctly join the table of months (or an individual MONTH from the table of dates).

Answer 2

You can try something like this

  select * from 
     (select 2000 as year union
      select 2001 as year union
      select 2009
     ) as years, 
     (select 1 as month union 
      select 2 as month union 
      select 3 as month union 
      select 4 as month union 
      select 5 as month union 
      select 6 as month union 
      select 7 as month union 
      select 8 as month union 
      select 9 as month
      )

 AS months WHERE year between 2001 AND 2008 OR (year=2000 and month>0) OR (year = 2009 AND month < 4) ORDER by year,month 

Answer 3

You can simply fill in the missing months after you have completed your request in the logic of your application.

Answer 4

You should definitely do this in your application and not in the DB layer. Just create an array of dates for the time range and combine the actual data with the empty dates that you previously created. See this answer to a similar question.

Answer 5

If you use PostgreSQL, you can combine date_trunc and generate_series to make very interesting grouping and series creation.

For example, you can use this to create a table of all dates for the last year:

 SELECT current_date - sa as date FROM generate_series(0,365,1) as s(a); 

Then you can use date_trunc to capture months and groups in the date_trunc'ed field:

 SELECT date(date_trunc('month', series.date)) as month, COUNT(*) as days FROM (SELECT current_date - sa as date FROM generate_series(0,365,1) as s(a)) series GROUP BY month; 

Answer 6

Create a table (e.g. tblMonths ) that includes all 12 months and use the LEFT JOIN (or RIGHT JOIN ) on it and your partial source data.

Check the link and this tutorial for how it works.

Answer 7

I would do something like this:

 SELECT COUNT(Order.OrderID) FROM Orders WHERE YEAR(Order.DateOrdered) > 2000 GROUP BY MONTH(Order.DateOrdered) 

This will give you the number of orders grouped for each month. Then, in your application, simply assign ZERO to the months in which data was not returned

I hope this helps

Answer 8

I execute the following query to generate months for a specific interval. For my case, it generates a list of months starting in 2013.

 SELECT date_format(@dt:= DATE_ADD( @dt, INTERVAL 1 MONTH),'%M %Y') date_string, @dt as date_full FROM (SELECT @dt := DATE_SUB(CAST(DATE_FORMAT('2013-05-01' ,'%Y-%m-01') AS DATE), INTERVAL 1 MONTH) ) vars, your_tables WHERE @dt<NOW() 

The competition is that it should be combined with a table containing enough rows to indicate the number of expected months. For example. if you need to generate the whole month in a particular year, you will need tables consisting of at least 12 rows.

For me it is a little straightforward. I joined it with my configuration table, consisting of 370 rows. That way, it can generate months in a year or days in a year if I need it. Changing the interval between months and day intervals will be simple, since I only need to change the interval from MONTH to DAY.