Displaying zero valued months with SQL

Question

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 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

Because the dates don't exist in your data, you need to construct a list of date values, and LEFT JOIN the ORDERS table to that list... — OMG Ponies, Apr 19 '10 at 19:31
if I read you correctly, here is a recent similar question http://stackoverflow.com/questions/2651249/wanted-to-get-all-dates-in-mysql-result — Unreason, Apr 19 '10 at 19:45
I don't do MySQL, so I don't want to post an answer, but in TSql, we'd do something like `Sum(ISNULL(O.Total, 0) * ISNULL(C.Rate, 0)) as Sales` — AllenG, Apr 19 '10 at 20:24

score 1 · Accepted Answer · answered Apr 19 '10 at 19:34

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.

answered Apr 19 '10 at 19:34

Mark Byers

811,555
193
1,581
1,452

I wound up doing this in PHP. Trying to do it in SQL would have made the original query complex beyond what I am able to handle. – user320691 Jun 01 '10 at 18:04

score 0 · Answer 2 · edited May 23 '17 at 12:11

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.

Displaying zero valued months with SQL

2 Answers2

Linked

Related