CODEWITHSUNDEEP
mysqlsqlcount
brianchrystal
brianchrystal

Reputation: 21

SQL - Count of a column until specific result

I have a table containing info on call attempts on contacts. The relevant fields are below:

Contact     Date        Result
John Smith  1/8/2013    VM
John Smith  1/9/2013    VM
John Smith  1/10/2013   Busy Signal
John Smith  1/11/2013   Hang Up                   <--- Connect
Jane Smith  1/8/2013    VM
Jane Smith  1/9/2013    Scheduled Call Back       <--- Connect
Jane Smith  1/10/2013   VM
John Doe    1/8/2013    Not Interested            <--- Connect
Jane Doe    1/8/2013    Busy Signal
Jane Doe    1/9/2013    Busy Signal
Jane Doe    1/10/2013   Busy Signal

I'm trying to get a count of the number of attempts until connect by contact (and not any attempts after the first connect), or the number of attempts so far if no connect has been made, and I'm having trouble. The results that would signify a connect in this case would be Hang Up, Scheduled Call Back and Not Interested.

What I am trying to get would look like:

Contact       Attempts
John Smith    4
Jane Smith    2
John Doe      1
Jane Doe      3

Is there a way to do this? Any help would be greatly appreciated.

Upvotes: 2

Views: 640

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

For MySQL:

SELECT 
    t.Contact, COUNT(*) AS Attempts
FROM 
    tableX AS t
  JOIN 
          ( SELECT DISTINCT contact
            FROM tableX
          ) AS d
      LEFT JOIN
          ( SELECT contact, 
                   MIN(date) AS date 
            FROM tableX
            WHERE result IN ('Hang Up', 'Scheduled Call Back', 'Not Interested')
            GROUP BY contact
          ) AS g
        ON d.contact = g.contact
    ON  t.contact = d.contact
    AND t.date <= COALESCE(g.date, '9999-12-31')
GROUP BY
    t.contact ;

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

You need to look into using GROUP BY:

SELECT Contact, COUNT(*) Attempts
FROM Contacts 
WHERE Result NOT IN ('Hang Up','Scheduled Call Back','Not Interested')
GROUP BY Contact

--EDIT

Didn't realize OP's request. And couldn't finish this before he accepted another answer...

Good luck.

Upvotes: 2

Related Questions