Displaying zero valued months with SQL

The following que开发者_开发问答ry returns values for months with sales but not for months where sales are zero:

SELECT DATE_FORMAT(O.OrderDate, "%b, %Y") AS MonthYear, SUM(O.Total * C.Rate) AS Sales 
FROM (Orders O INNER JOIN OrdersStates OS ON O.OrderID = OS.OrderID)
INNER JOIN Users U ON U.UserID = O.UserID
INNER JOIN UsersDescription UD ON U.UserID = UD.UserID
INNER JOIN States S ON S.StateID = OS.StateID INNER JOIN Currencies C ON O.Currency = C.Abb 
WHERE O.OrderDate >= '2009-10-01 00:00:00' AND O.OrderDate < '2010-04-19 23:59:59' 
GROUP BY MonthYear ORDER BY MonthYear

The goal is to make it return a zero value in months where there are no sales. Can anyone see what is missing?

Thank you in advance,

Orville

While not impossible, it's not easy to do this in SQL.

Since it is mostly a presentation issue it would probably be easier to add the missing rows in the client after you have executed the query in your question by iterating over the results and checking for missing months.

Since you are using mysql there will be no clean solution which is purely SQL if there will be some months with no orders for some Users.

Basically the minimum you need for pure sql (in mysql and other RDBMS that don't support recursive queries) is a temp table with a sequence of integers, for each month.

Building such table could be done as regular part of monthly reporting, so it is not such an artificial requirement - at the same time you can track some other information related to this data (for example you can define when your financial month begins and ends, or you could track exchange rates, etc..).

On the other hand if you really, really want pure sql take a look at this answer - it is a hack and you will be limited by the maximum date range and the performance is not stellar, but this is the best I found for mysql.