MySQL: Is it possible to 'fill' a SELECT with values without a table?

Question

I need to display the total of 'orders' for each year and month. But for some months there is no data, but I DO want to display that month (with a total value of zero). I could make a helpertable 'months' with 12 records for each year, but is there maybe a way to get a range 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 anybody have an idea how to do this? Can you use SELECT with some kind of formula, to 'create' data on the fly?!

UPDATE:

Found this myself: generate days from date range

The accepted answer in this question is kind of what I'm looking for. Maybe not the easiest method, but it does what I want: fill a select with data, based on a formula....

To 'create' a table on the fly with all 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 (a.a + (10 * b.a) + (100 * c.a)) 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 must admit, this is VERY exotic, but it DOES work...

I can use this select to join it with my 'orders'-table and get the totals for each month, even when there is no data in a certain month.

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

Answer 1

You could 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 2

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

If you need a series of integers, use a static table of integers. If you need a series of dates, months or whatever, use a calendar table. Unless you are dealing with some truly extraordinary requirements, a static table is the way to go.

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

If you have those tables in place, it becomes easy to solve your query.

1. Aggregate the order data to MONTH.
2. Right join to the table of months (or distinct MONTH from the table of dates)

Answer 3

You could just fill in the missing months after you've done your query in your application logic.

Answer 4

I do following query to generate months in a given interval. For my case it generate list of month started from may 2013 until now.

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 concern is, it should be joined with table containing sufficient rows to supply number of month you expected. E.g. if you need to generate all month in a particular year, you will need a tables consisting at least 12 rows.

For me it is a bit straight forward. I joined it with my configuration table, consisting around 370 rows. So it could generate months in a year, or days in a year if I need it. Changing from month interval into days interval would be easy, as I need only to change the interval from MONTH to DAY.

Answer 5

If you're using PostgreSQL, you can combine both date_trunc and generate_series to do some very fun grouping and series generation.

For example, you could use this to generate a table of all dates in the last year:

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

Then, you could use date_trunc to grab the months and group by that date_trunc'ed field:

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

Answer 6

You should most definitely do this in your application rather than the DB layer. Simply create an array of dates for the time range, and merge the actual data with the empty dates you pre-created. See this answer to similar question

Answer 7

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

Check out the reference and this tutorial for how this works.

Answer 8

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 by each month. Then in you application simply assign a ZERO to the months in which no data was returned

I hope this Helps

Answer 9

Query on static data MySQL.

You can select static data from hardcoded list with table by this query

SELECT *
FROM (
    values row('Hamza','23'), row('Ali', '24')
) t1 (name, age);