Problem with a complex SQL query - select total user count, invoice count for each month from specified period
Hello everyone,
I have a problem with a mysql query and hope someone to help me.
I have the following tables:
CREATE TABLE USER
(
ID VARCHAR(80) NOT NULL,
REGISTRATION_DATE DATE NOT NULL,
.....
PRIMARY KEY(ID)
);
CREATE TABLE INVOICES
(
USER_ID VARCHAR(80) NOT NULL,
INVOICE_NUMBER VARCHAR(50) NOT NULL,
CREATION_DATE DATE NOT NULL,
........
PRIMARY KEY(USER_ID, INVOICE_NUMBER),
FOREIGN KEY(USER_ID) REFERENCES USER(ID)
);
I need following functionality: I choose start and end date (period of time) e.g. 01.02.2013-31.05.2013 as parameters for the query and want to see:
1. Total count of users for each month from the chosen period (! Not the count per month, but the the total count of users up to (and including) the month.).
2. Count of users who created at least one invoice in this month, again for each month.
3. Count of invoices for each month (here the count per month).
Example: Period: 01.02.2013-31.05.2013
Resultset:
Month | Year | Total count of users | Count of users who created at least one invoice in this month | Count of invoices in this month
Febr | 2013 | 20 | 12 | 45
March | 2013 | 20 | 4 | 22
April | 2013 | 23 | 20 | 30
May | 2013 | 28 | 27 | 69
Is it possible to write such query?
I have tried this:
SELECT YEAR, MONTH, @VAR := @VAR + TOTS.COUNT AS USERS_TOTAL
FROM (
SELECT
YEAR(REGISTRATION_DATE) AS YEAR,
MONTH(REGISTRATION_DATE) AS MONTH,
COUNT(ID) AS COUNT
FROM USER
WHERE REGISTRATION_DATE <= '2013-05-31'
GROUP BY YEAR, MONTH
) AS TOTS, (SELECT @VAR := 0) AS INC
I get the total number of users but only if there are some new registered users. I mean, if total count of users in Ferb. and in March is 20, in April 21, I get
Febr. - 20, no information for March and April 21, I need information for each month from the choosen period.
With this query I get the count of users who created at least one invoice but again if the count is the same as the count in the previous months - no information:
SELECT YEAR(CREATION_DATE) as YEAR, MONTH(CREATION_DATE) as MONTH,
COUNT(DISTINCT CUSTOMER_ID)
FROM INVOICES
WHERE CREATION_DATE >= '2013-02-01' AND CREATION_DATE <='2013-05-31'
GROUP BY YEAR, MONTH
With this query I get the count of invoices for each month in the choosen period:
SELECT YEAR(CREATION_DATE) as YEAR, MONTH(CREATION_DATE) as MONTH, COUNT(INVOICE_NUMBER)
FROM INVOICES
WHERE CREATION_DATE >= '2013-02-01' AND CREATION_DATE <='2013-05-31'
GROUP BY YEAR, MONTH
I want to make this work in a single query and as I said, want to have information for each month, even if the information is the same as in the previous month.
How could I achieve the result I want, any ideas?
Cheers