You will need to use subqueries in the FROM clause or a series of WITH statements before the main SELECT statement (if your DBMS supports this notation). You will also need to correct your inquiries so that you do not report on those who joined in August 2009, as well as those who joined in August 2010.
Simplified answer
The “tighter answer" below is the corrected original request, and I left it because it shows how I developed the answer. The following answer is simpler; it uses the fact that COUNT (Column) returns 0 if there are no nonzero values in the column to be counted.
It uses the BaseCounts table to control which aggregates should appear:
CREATE TEMP TABLE BaseCounts ( NumOrders CHAR(2) NOT NULL PRIMARY KEY ); INSERT INTO BaseCounts VALUES("0 "); INSERT INTO BaseCounts VALUES("1 "); INSERT INTO BaseCounts VALUES("2 "); INSERT INTO BaseCounts VALUES("3 "); INSERT INTO BaseCounts VALUES("4 "); INSERT INTO BaseCounts VALUES("5+"); SELECT B.NumOrders, COUNT(N.MemberID) AS NumNewMembers FROM BaseCounts AS B LEFT OUTER JOIN (SELECT MemberID, CASE WHEN NumOrders < 5 THEN CAST(NumOrders AS CHAR(2)) ELSE "5+" END AS NumOrders FROM (SELECT M.MemberID, COUNT(O.OrderID) AS NumOrders FROM Members AS M LEFT OUTER JOIN Orders AS O ON M.MemberID = O.MemberID AND YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8 WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8 GROUP BY M.MemberID ) AS NMO ) AS N ON B.NumOrders = N.NumOrders GROUP BY B.NumOrders ORDER BY B.NumOrders;
CREATE TEMP TABLE for IBM Informix Dynamic Server (version 11.50 used for testing). The table disappears at the end of the session (or when it is explicitly disabled), and it is closed to the session. It can be a permanent base table (instead of the TEMP keyword).
A sub-request labeled NMO (for new member orders) is very important. The filter condition on O.DateCreated should appear in the ON clause, and not in the WHERE clause; otherwise, you will not get the null values that are needed. The designation COUNT (Column) is used twice.
The explanation in the more complex answer shown below will help you understand the details that are not explained in this simpler answer. Although "simpler," I would not consider it "simple." The whole answer shows the importance of repeating your design; I could not easily get a simpler answer without going through the effort to create a more complex one.
Tighter answer
This was the original development of the answer. I believe that he can still use it to show how I approached this problem. Moreover, as a basis, it was quite simple to remove additional material while developing a simpler answer above.
Counting zeros is also unexpectedly difficult, like for "5+". So, let's figure it out in stages.
New members with 1 or more purchases
SELECT M.MemberID, COUNT(*) AS NumOrders FROM Members AS M JOIN Orders AS O ON M.MemberID = O.MemberID WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8 AND YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8 GROUP BY M.MemberID
Call this list "NZO" (for "non-zero orders"). Please note that LEFT OUTER JOIN assigns people to group "1", even if they do not place orders, and not the desired result.
New members with 0 purchases
SELECT M.MemberID, 0 AS NumOrders FROM Members AS M WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8 AND NOT EXISTS (SELECT * FROM Orders AS O WHERE YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8 AND O.MemberID = M.MemberID )
This is a nasty request due to a correlated subquery, but it avoids referencing NZO. An alternative would be to find a list of participants who joined the reference month and subtract from this a list of members with 1 or more orders (NZO).
Call this list "WZO" (for "with zero orders").
Obviously, NZO and WZO do not have common members - UNION or UNION ALL of them give a list of new members and the number of orders that they place.
New members in six categories
SELECT MemberID, CAST(NumOrders AS CHAR(2)) AS NumOrders FROM WZO UNION SELECT MemberID, CASE WHEN NumOrders < 5 THEN CAST(NumOrders AS CHAR(2)) ELSE "5+" END AS NumOrders FROM NZO
Jobs fix the type problem here - the NumOrders column is a numeric type, and the result should be a string.
Call this list of NMC (new members in categories).
Summarize
SELECT NumOrders, COUNT(*) AS NumNewMembers FROM NMC GROUP BY NumOrders ORDER BY NumOrders;
Penultimate request
Assembling the various bits and pieces above - and getting the right bits in the right places - gives the following query:
SELECT NumOrders, COUNT(*) AS NumNewMembers FROM (SELECT MemberID, CAST(NumOrders AS CHAR(2)) AS NumOrders FROM (SELECT M.MemberID, 0 AS NumOrders FROM Members AS M WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8 AND NOT EXISTS (SELECT * FROM Orders AS O WHERE YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8 AND O.MemberID = M.MemberID ) ) AS WZO UNION SELECT MemberID, CASE WHEN NumOrders < 5 THEN CAST(NumOrders AS CHAR(2)) ELSE "5+" END AS NumOrders FROM (SELECT M.MemberID, COUNT(*) AS NumOrders FROM Members AS M JOIN Orders AS O ON M.MemberID = O.MemberID WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8 AND YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8 GROUP BY M.MemberID ) AS NZO ) AS NMC GROUP BY NumOrders ORDER BY NumOrders;
This completed request was successfully completed using IBM Informix Dynamic Server 11.50. For the obtained sample data (see below), I got the result:
numorders numnewmembers CHAR(2) DECIMAL(15,0) 0 1 1 1 2 1 3 1 4 1 5+ 2
The general scheme of building a query in parts should help you develop your own queries in the future. In particular, you can check for different segments of a query as you go.
It may seem easier for you to work with the date by creating the first and last days in the month of interest to you, and then running a query for these ranges, which is also more flexible since it can do quarters or crescents or periods spanning two months.
Also note that if there are no new members that put, say, 2 orders per month to which they join, then there will be no row as a result. You can fix this problem - to solve this problem is not easy.
Work with "No new members made N purchases"
There are probably several ways to get a null-counted string for missing items. The technique that I usually use is to create a table containing the rows that I want to display, something like this - where I created the temporary tables to hold the result of each of the named expressions in the main part of the answer. This is a variant of the BaseCounts table, shown in a simpler answer; This version does not need the NumNewMembers column, while this version does.
CREATE TEMP TABLE BaseCounts ( NumOrders CHAR(2) NOT NULL, NumNewMembers DECIMAL(15,0) NOT NULL ); INSERT INTO BaseCounts VALUES("0 ", 0); INSERT INTO BaseCounts VALUES("1 ", 0); INSERT INTO BaseCounts VALUES("2 ", 0); INSERT INTO BaseCounts VALUES("3 ", 0); INSERT INTO BaseCounts VALUES("4 ", 0); INSERT INTO BaseCounts VALUES("5+", 0); SELECT NumOrders, MAX(NumNewMembers) AS NumNewMembers FROM (SELECT * FROM BaseCounts UNION SELECT NumOrders, COUNT(*) AS NumNewMembers FROM NMC GROUP BY NumOrders ) GROUP BY NumOrders ORDER BY NumOrders;
The second query in UNION in the FROM clause is the previous “final” response, using a temporary table for intermediate results.
Final request
When writing out, to avoid a temporary table, the query will look like this:
SELECT NumOrders, MAX(NumNewMembers) FROM (SELECT * FROM BaseCounts UNION SELECT NumOrders, COUNT(*) AS NumNewMembers FROM (SELECT MemberID, CAST(NumOrders AS CHAR(2)) AS NumOrders FROM (SELECT M.MemberID, 0 AS NumOrders FROM Members AS M WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8 AND NOT EXISTS (SELECT * FROM Orders AS O WHERE YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8 AND O.MemberID = M.MemberID ) ) AS WZO UNION SELECT MemberID, CASE WHEN NumOrders < 5 THEN CAST(NumOrders AS CHAR(2)) ELSE "5+" END AS NumOrders FROM (SELECT M.MemberID, COUNT(*) AS NumOrders FROM Members AS M JOIN Orders AS O ON M.MemberID = O.MemberID WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8 AND YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8 GROUP BY M.MemberID ) AS NZO ) AS NMC GROUP BY NumOrders ) GROUP BY NumOrders ORDER BY NumOrders;
When using a modified dataset, I get the result:
NumOrders NumNewMembers CHAR(2) DECIMAL(15,0) 0 1 1 1 2 1 3 0 4 2 5+ 2
Some DBMSs provide other, possibly more convenient, ways to create table values, such as the BaseCounts table.
One alternative method that could be considered is some kind of outer join using "COUNT (column)" instead of "COUNT (*)". When you use "COUNT (column)", the query only considers rows with a non-zero value for "column", so the outer join that generates zero in the column will give "COUNT (column)" zero for null. However, you still need a list of links from any lines that should appear in the output so that you can determine when something is missing from the dataset. This is provided by the BaseCounts table in my exposure.
WITH clause
In addition, as noted above, the SQL standard and some DBMSs provide a WITH clause that allows you to create named intermediate results that can then be used in the final query (or, indeed, later in the WITH clause)
WITH <name1> AS (<query1>), <name2>(<named-columns>) AS (<query2>), ... SELECT ... FROM <name1> JOIN <name2> ON ...
Using this, we could write the following (unverified) SQL:
WITH NZO AS ( SELECT M.MemberID, COUNT(*) AS NumOrders FROM Members AS M JOIN Orders AS O ON M.MemberID = O.MemberID WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8 AND YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8 GROUP BY M.MemberID), WZO AS ( SELECT M.MemberID, 0 AS NumOrders FROM Members AS M WHERE YEAR(M.DateCreated) = 2010 AND MONTH(M.DateCreated) = 8 AND NOT EXISTS (SELECT * FROM Orders AS O WHERE YEAR(O.DateCreated) = 2010 AND MONTH(O.DateCreated) = 8 AND O.MemberID = M.MemberID )), NMC AS ( SELECT MemberID, CAST(NumOrders AS CHAR(2)) FROM WZO UNION SELECT MemberID, CASE WHEN NumOrders < 5 THEN CAST(NumOrders AS CHAR(2)) ELSE "5+" END AS NumOrders FROM NZO), NZC AS ( SELECT NumOrders, COUNT(*) AS NumNewMembers FROM NMC GROUP BY NumOrders) SELECT NumOrders, MAX(NumNewMembers) FROM (SELECT * FROM NZC UNION SELECT * FROM BaseCounts ) GROUP BY NumOrders ORDER BY NumOrders;
Data examples
Tables
CREATE TABLE Members ( MemberID INTEGER NOT NULL PRIMARY KEY, DateCreated DATE NOT NULL ); CREATE TABLE Orders ( OrderID INTEGER NOT NULL PRIMARY KEY, DateCreated DATE NOT NULL, MemberID INTEGER NOT NULL REFERENCES Members );
Users
INSERT INTO Members VALUES(1, '2009-08-03'); INSERT INTO Members VALUES(2, '2010-08-03'); INSERT INTO Members VALUES(3, '2010-08-05'); INSERT INTO Members VALUES(4, '2010-08-13'); INSERT INTO Members VALUES(5, '2010-08-15'); INSERT INTO Members VALUES(6, '2010-08-23'); INSERT INTO Members VALUES(7, '2010-08-23'); INSERT INTO Members VALUES(8, '2010-08-23'); INSERT INTO Members VALUES(9, '2010-09-03');
Orders
INSERT INTO Orders VALUES(11, '2010-08-03', 1); INSERT INTO Orders VALUES(33, '2010-08-03', 3); INSERT INTO Orders VALUES(44, '2010-08-05', 4); INSERT INTO Orders VALUES(45, '2010-08-06', 4); INSERT INTO Orders VALUES(56, '2010-08-11', 5); INSERT INTO Orders VALUES(57, '2010-08-13', 5); INSERT INTO Orders VALUES(58, '2010-08-23', 5); --For testing 0 members with 3 orders (and 2 with 4 orders), add: --INSERT INTO Orders VALUES(51, '2010-08-09', 5); INSERT INTO Orders VALUES(61, '2010-08-05', 6); INSERT INTO Orders VALUES(62, '2010-08-15', 6); INSERT INTO Orders VALUES(63, '2010-08-15', 6); INSERT INTO Orders VALUES(64, '2010-08-25', 6); INSERT INTO Orders VALUES(71, '2010-08-03', 7); INSERT INTO Orders VALUES(72, '2010-08-03', 7); INSERT INTO Orders VALUES(73, '2010-08-03', 7); INSERT INTO Orders VALUES(74, '2010-08-03', 7); INSERT INTO Orders VALUES(75, '2010-08-03', 7); INSERT INTO Orders VALUES(81, '2010-08-03', 8); INSERT INTO Orders VALUES(82, '2010-08-03', 8); INSERT INTO Orders VALUES(83, '2010-08-03', 8); INSERT INTO Orders VALUES(84, '2010-08-03', 8); INSERT INTO Orders VALUES(85, '2010-08-03', 8); INSERT INTO Orders VALUES(86, '2010-08-03', 8); INSERT INTO Orders VALUES(91, '2010-09-03', 9);