Mysql – Return results of two MySQL query in only one

aggregatejoin;mariadbMySQLstatistics

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:

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.