Displaying Zero Valued Months With Sql

The following query 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 S

Solution 1:

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.

Solution 2:

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.