MYSQL query to find all duplicated record with specified column groupings

Question

I have a table that holds all the emails sent for email campaigns:

ID | campaign_id | date_sent | date_delivered | email | status

Status holds delivery information, DELIVERED or UNDELIVERED. I want to find all email addresses that their status is UNDELIVERED for the last five campaigns.

For example, [email protected] used to be ok and received all campaigns, but now the email address does not exist and the last 5 campaigns status is UNDELIVERED.

So far I am doing:

SELECT *, count(id) as occ FROM message_details WHERE status='FAILED' GROUP BY email

But this is showing the total of undelivered for every address and not the last 5.

Answer 1

This may be a bit tricky to read. Basically, what it is trying to do is to get top 5 message_details for each email then select those that have number of failed times equal to 5.

Haven't got time to test this code though.

SELECT *, count(thirdTable.id) as failTimes FROM
(SELECT rs.id, rs.Status, rs.email
    FROM (
        SELECT *, Rank() 
          over (Partition BY Section
                ORDER BY RankCriteria DESC ) AS Rank
        FROM message_details 
        ) rs WHERE Rank <= 5 ) as thirdTable
Where thirdTable.status = 'FAILED' GROUP BY thirdTable.email HAVING failTimes = 5

Answer 2

Try this,

SELECT TOP 5 CampID,COUNT(id) Occ FROM dbo.message_details
WHERE Status    = 'FAILED'
GROUP BY CampID
ORDER BY CampID desc

Answer 3

This is very similar to Using LIMIT within GROUP BY to get N results per group?, I've tried some answer to that post. What you want to fetch is last 5 fail sent campaign of all emails, so it seems that your sql SELECT *, count(id) as occ FROM message_details WHERE status='FAILED' GROUP BY email can not make it actually.

Assume your data like this:

message_details
---
| ID | campaign_id | date_sent | date_delivered | email | status | 
| 1 | 1 | 2016-04-21 | null | [email protected] | FAILED | 
| 2 | 2 | 2016-04-22 | null | [email protected] | FAILED | 
| 3 | 3 | 2016-04-23 | null | [email protected] | FAILED | 
| 4 | 4 | 2016-04-24 | null | [email protected] | FAILED | 
| 5 | 5 | 2016-04-25 | null | [email protected] | FAILED | 
| 6 | 6 | 2016-04-26 | null | [email protected] | FAILED | 
| 7 | 1 | 2016-04-21 | null | [email protected] | FAILED | 
| 8 | 2 | 2016-04-22 | null | [email protected] | FAILED | 
| 9 | 3 | 2016-04-23 | null | [email protected] | FAILED | 
| 10 | 4 | 2016-04-24 | null | [email protected] | FAILED | 
| 11 | 5 | 2016-04-25 | null | [email protected] | FAILED | 
| 12 | 6 | 2016-04-26 | null | [email protected] | FAILED | 

Then run this sql,

SELECT t1.email, t1.campaign_id
FROM message_details t1
LEFT JOIN message_details t2 ON t1.email = t2.email AND t1.date_sent <= t2.date_sent
WHERE t1.status='FAILED'
GROUP BY t1.email, t1.campaign_id, t1.date_sent
HAVING COUNT(*) <= 5

I got below result set,

| email | campaign_id | 
| [email protected] | 2 | 
| [email protected] | 3 | 
| [email protected] | 4 | 
| [email protected] | 5 | 
| [email protected] | 6 | 
| [email protected] | 2 | 
| [email protected] | 3 | 
| [email protected] | 4 | 
| [email protected] | 5 | 
| [email protected] | 6 | 

I don't know if this works for you, but you can take a look on that link above. And if you've got some solution for your post, share it to me will be appreciated.