MySQL Forums
Forum List  »  General

Problem with a complex SQL query - select total user count, invoice count for each month from specified period
Posted by: Radoslav Yankov
Date: May 30, 2013 02:44AM

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

Options: ReplyQuote


Subject
Written By
Posted
Problem with a complex SQL query - select total user count, invoice count for each month from specified period
May 30, 2013 02:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.