Re: Problem with a complex SQL query - select total user count, invoice count for each month from specified period
Hi again,
I have a problem with another query, which, I think, should be very similar to the first one, but I can't get the expected results. This time I need to group not only
by Year and Month but also by Postal code.
That are all tables and datasets:
CREATE DATABASE TEST_DB;
CREATE TABLE CITY
(
POSTALCODE VARCHAR(15) NOT NULL,
CITYNAME VARCHAR(50) NOT NULL,
COUNTRY VARCHAR(30),
PRIMARY KEY(POSTALCODE)
);
CREATE TABLE USER
(
ID VARCHAR(80) NOT NULL,
REGISTRATION_DATE DATE NOT NULL,
POSTALCODE VARCHAR(15),
PRIMARY KEY(ID),
FOREIGN KEY(POSTALCODE) REFERENCES CITY(POSTALCODE)
);
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)
);
CREATE TABLE REPORTCAL
(
YR SMALLINT UNSIGNED,
MO TINYINT UNSIGNED,
PRIMARY KEY(YR, MO)
);
INSERT INTO CITY(POSTALCODE, CITYNAME, COUNTRY) VALUES ('1111', 'City 1', 'Country 1');
INSERT INTO CITY(POSTALCODE, CITYNAME, COUNTRY) VALUES ('2222', 'City 2', 'Country 2');
INSERT INTO CITY(POSTALCODE, CITYNAME, COUNTRY) VALUES ('3333', 'City 3', 'Country 3');
INSERT INTO CITY(POSTALCODE, CITYNAME, COUNTRY) VALUES ('4444', 'City 4', 'Country 4');
INSERT INTO CITY(POSTALCODE, CITYNAME, COUNTRY) VALUES ('5555', 'City 5', 'Country 5');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('A', '2012-01-10', '2222');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('B', '2012-01-12', '3333');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('C', '2012-01-20', '2222');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('D', '2012-01-26', '2222');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('E', '2012-02-01', '1111');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('F', '2012-03-02', '1111');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('G', '2012-03-11', '3333');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('H', '2012-03-12', '5555');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('I', '2012-03-22', '4444');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('J', '2012-03-24', '3333');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('K', '2012-09-09', '5555');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('L', '2012-09-23', '4444');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('M', '2012-12-13', '1111');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('N', '2012-12-16', '4444');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('O', '2013-04-13', '5555');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('P', '2013-04-28', '2222');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('Q', '2013-05-28', '5555');
INSERT INTO USER(ID, REGISTRATION_DATE, POSTALCODE) VALUES ('R', '2013-05-29', '3333');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '1', '2012-01-10');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '2', '2012-01-20');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '3', '2012-03-12');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '4', '2012-12-16');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '5', '2013-02-18');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '6', '2013-04-08');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('A', '7', '2013-04-08');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '1', '2012-02-08');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '2', '2012-04-28');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '3', '2012-06-25');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '4', '2012-07-09');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('D', '5', '2013-05-28');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '1', '2012-12-21');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '2', '2012-12-23');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '3', '2012-12-26');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '4', '2013-02-23');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '5', '2013-03-13');
INSERT INTO INVOICES(USER_ID, INVOICE_NUMBER, CREATION_DATE) VALUES ('M', '6', '2013-03-23');
INSERT INTO REPORTCAL(YR, MO) VALUES(2011, 12);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 1);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 2);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 3);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 4);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 5);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 6);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 7);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 8);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 9);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 10);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 11);
INSERT INTO REPORTCAL(YR, MO) VALUES(2012, 12);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 1);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 2);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 3);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 4);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 5);
INSERT INTO REPORTCAL(YR, MO) VALUES(2013, 6);
I need following results:
Period: Janury 2012-May 2013
Expected results:
Year | Month | Postal code |Total count of users | Count of users who created at least one invoice in this month | Count of invoices in this month |
2012| Jan | 1111 | 0 | 0 | 0 |
2012| Feb | 1111 | 1 | 0 | 0 |
2012| March | 1111 | 2 | 0 | 0 |
2012| April | 1111 | 2 | 0 | 0 |
2012| May | 1111 | 2 | 0 | 0 |
2012| June | 1111 | 2 | 0 | 0 |
2012| July | 1111 | 2 | 0 | 0 |
2012|August | 1111 | 2 | 0 | 0 |
2012| Sept | 1111 | 2 | 0 | 0 |
2012| Oct | 1111 | 2 | 0 | 0 |
2012| Nov | 1111 | 2 | 0 | 0 |
2012| Dec | 1111 | 3 | 1 | 3 |
2013| Jan | 1111 | 3 | 0 | 0 |
2013| Feb | 1111 | 3 | 1 | 1 |
2013| March| 1111 | 3 | 1 | 2 |
2013| April | 1111 | 3 | 0 | 0 |
2013| June | 1111 | 3 | 0 | 0 |
In January there are no users from city with postal code 1111. In February we have one user from city with postal code 1111 but this user didn't create any
invoices. In December we have one new user from city with postal code 1111, so the total count of users with postal code 1111 is 2 + 1 = 3. In December one
of the 3 users from city with postalcode 1111 created 3 invoices.
That's the logic, I hope you understand it. I need the same for the another postal codes - 2222, 3333, 4444, 5555 following this logic.
It's very similar to the first query, I need group by Postal code, Year and Month.
Could you give me some advice, how to write this query. Thanks again in advance! :-)