I am running two seperate queries to get statistics from 3 different tables:
- contacts
- campaigns
- sent_messages
The first query counts all contacts that are marked as "stop" in the contacts table (results are grouped by month and limited to last 12 months).
SELECT TIMESTAMP(sent_date) AS timestamp, COUNT(contacts.id) AS stopped
FROM contacts
JOIN campaigns
ON campaigns.list_id = contacts.list_id
WHERE STOP = 1 AND campaigns.user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;
The other query counts the status codes of sent messages (also grouped by month and limited to last 12):
SELECT
TIMESTAMP(sent_date) AS timestamp,
COUNT(sent_messages.id) AS messages,
SUM(IF(sent_messages.status = 1, 1, 0)) AS received,
SUM(IF(sent_messages.status > 1, 1, 0)) AS errored,
FROM campaigns
JOIN contacts
ON campaigns.list_id = contacts.list_id
WHERE user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;
Now I'm having a hard time grouping these two queries in a single one. I have tried the following but it takes ages (full table scan) and returns uncoherent results.
SELECT
TIMESTAMP(sent_date) AS timestamp,
COUNT(sent_messages.id) AS messages,
SUM(IF(sent_messages.status = 1, 1, 0)) AS received,
SUM(IF(sent_messages.status > 1, 1, 0)) AS errored,
SUM(IF(stop = 1, 1, 0)) AS stopped
FROM campaigns
JOIN contacts
ON campaigns.list_id = contacts.list_id
JOIN sent_messages
ON campaigns.id = sent_messages.campaign_id
WHERE user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;
So I'd like to know why I get these results (maybe the second JOIN ends up creating duplicates and everything ends up being counted several times?), and how could I fix that (if at all possible).
For reference, the tables involved:
CREATE TABLE contacts (
id BIGINT UNSIGNED AUTO_INCREMENT,
list_id INT UNSIGNED NOT NULL,
telephone CHAR(12) NOT NULL,
firstname VARCHAR(50),
lastname VARCHAR(50),
attributes VARCHAR(10000),
stop BOOLEAN DEFAULT 0,
PRIMARY KEY (id),
INDEX contact_ind_list_id (list_id),
INDEX contact_ind_stop (stop),
UNIQUE KEY contact_index_telephone_and_list (telephone, list_id),
CONSTRAINT contact_fk_list
FOREIGN KEY (list_id)
REFERENCES lists (id)
ON DELETE CASCADE
);
CREATE TABLE campaigns (
id INT UNSIGNED AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
list_id INT UNSIGNED,
name VARCHAR(50),
text VARCHAR(500),
send_date TIMESTAMP,
sent_date TIMESTAMP,
sender_name VARCHAR(11),
status TINYINT UNSIGNED DEFAULT 0, -- 0 draft, 1 to be sent, 2 sent
batch_number SMALLINT UNSIGNED DEFAULT 0,
PRIMARY KEY (id),
INDEX campaign_ind_owner (user_id),
INDEX campaign_ind_push_id (push_id),
UNIQUE KEY campaign_ind_list_and_name (list_id, name),
CONSTRAINT campaign_fk_owner
FOREIGN KEY (user_id)
REFERENCES users (id)
ON DELETE CASCADE,
CONSTRAINT campaign_fk_list
FOREIGN KEY (list_id)
REFERENCES lists (id)
ON DELETE CASCADE
);
CREATE TABLE sent_messages (
id BIGINT UNSIGNED AUTO_INCREMENT,
campaign_id INT UNSIGNED NOT NULL,
telephone CHAR(12) NOT NULL,
status TINYINT NOT NULL, -- 0 = waiting / 1 = received / 2 = npai / 3 = net_error (error_credits, unknown_error)
message TINYINT NOT NULL,
PRIMARY KEY (id),
INDEX sent_messages_ind_campaign (campaign_id),
UNIQUE KEY sent_messages_ind_campaign_and_telephone (campaign_id, telephone),
CONSTRAINT sent_messages_fk_campaign
FOREIGN KEY (campaign_id)
REFERENCES campaigns (id)
ON DELETE CASCADE
);
Best Answer
First, clean up the GROUP BY:
The value of
timestamp
will be some random value. Ditto for sorting onsent_date
, which is not available after theGROUP BY
. Instead, consider:(Note: "1" in these contexts refers to the 1st column of the
SELECT
.)Try that; you will see that it is OK.
Then, to fix the original question:
I'm not sure what to JOIN on; you'll still need an
ORDER BY
on the outside.