This is my setup:
Emails_Table
| ID | user_id | campaign_id | status | date |
---------------------------------------------------------------------------
| 100 | 7 | 12 | sent | 2017-05-29 |
| 101 | 8 | 11 | sent | 2017-06-19 |
| 104 | 9 | 14 | fail | 2017-06-22 |
| 105 | 7 | 12 | fail | 2017-07-17 |
| 107 | 9 | 14 | fail | 2017-07-25 |
| 108 | 7 | 15 | sent | 2017-12-10 |
| 109 | 9 | 2 | others | 2018-01-02 |
| 110 | 9 | 17 | sent | 2018-01-11 |
| 111 | 9 | 14 | fail | 2018-01-22 |
Users_Table
| user_id | username |
---------------------------
| 5 | john |
| 7 | james |
| 8 | maria |
| 9 | rex |
Campaigns_Table
| campaign_id | campaign_name |
---------------------------------------
| 2 | Sample 2nd Campaign |
| 11 | Sample 11 |
| 12 | Twelve |
| 14 | Sample #14 |
| 15 | Lala Campaign |
| 17 | Meow Campaign |
| 18 | Cat GIFs Campaign |
How can I get this kind of result? I am filtering using date range and the records are grouped by user_id.
Result
| user_id | username | total_sent | total_failed |
---------------------------------------------------
| 7 | james | 2 | 1 |
| 8 | maria | 1 | 0 |
| 9 | rex | 1 | 3 |
My starting query structure is like this:
SELECT Emails_Table.user_id, username, COUNT/SUM(total_sent), COUNT/SUM(total_failed)
FROM Emails_Table
LEFT JOIN Users_Table ON Emails_Table.user_id=Users_Table.user_id
WHERE (date BETWEEN 2017-05-29 AND 2018-01-22)
GROUP BY user_id
As you can see, I have difficulty in implementing COUNT/SUM(total_sent) and COUNT/SUM(total_failed). Can someone suggest me on how to achieve this?
