0

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
);
1

First, clean up the GROUP BY:

SELECT  TIMESTAMP(sent_date) AS timestamp, COUNT(contacts.id) AS stopped
    ...
    GROUP BY  YEAR(sent_date), MONTH(sent_date)
    ORDER BY  sent_date DESC

The value of timestamp will be some random value. Ditto for sorting on sent_date, which is not available after the GROUP BY. Instead, consider:

SELECT DATE_FORMAT(sent_date, "%Y-%m") AS timestamp, COUNT(contacts.id) AS stopped
    ...
    GROUP BY 1
    ORDER BY 1

(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:

SELECT ...
    FROM ( SELECT ... ((the first query, cleaned up)) ) a
    JOIN ( SELECT ... ((the second query, cleaned up)) ) b ON a.? = b.?
    ORDER BY ?

I'm not sure what to JOIN on; you'll still need an ORDER BY on the outside.

  • I especially loved the DATE_FORMAT(sent_date, "%Y-%m"), thank you! – Buzut May 24 at 8:40
  • 1
    @Buzut - Here's another way -- LEFT(sent_date, 7) - fewer keystrokes, similar speed, etc. – Rick James May 24 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.