Mysql Counting most recent consecutive number rows that match for multiple id's

Question

the Q: Mysql Counting the consecutive number rows that match has touched on this but would like some help expanding it.

I have multiple Members in a payments table and need a count of most recent Failed Payments where:

1 = Success, 2 = Fail

It must be based on most recent payments, not overall count! So a person could have had failures but count would be zero if most recent payment was Successful.

CREATE TABLE Payment
(`pID` int, `memID` int, `pStatus` int, )
;

INSERT INTO Payment
(`pID`, `memID`, `pStatus)
VALUES
(1, 1, 1001),
(2, 1, 1001),
(3, 1, 1001),
(4, 2, 1001),
(5, 2, 1001),
(6, 1, 1002),
(7, 2, 1002),
(8, 2, 1002),
(9, 1, 1002),
(10, 1, 1002),
(11, 2, 1003),
(12, 1, 1003),
(13, 2, 1003),
(14, 1, 1003),
(15, 2, 1003),
(16, 2, 1004),
(17, 2, 1004),
(18, 2, 1004),
(19, 2, 1004),
(20, 2, 1004);

Retun should be:

memId | failCount
1001  |  2
1002  |  0
1003  |  1
1004  |  5

We don't see any DATETIME or TIMESTAMP columns, so are we to determine that a payment is more "recent" than another payment by the value in the `pID` column; a greater value of `pID` indicates the payment is more recent than a row with a `pID` value that is lower? — spencer7593, Aug 29 '14 at 02:02
So? What stops you from writing such a query? Where are you stuck? — PM 77-1, Aug 29 '14 at 02:03
Do you want to count `2`s in a reverse order of `pID` until `1` is found? — PM 77-1, Aug 29 '14 at 02:05
If you're going to reference another question, please paste a link to the question, not just its title. SO will automatically insert the title. — Barmar, Aug 29 '14 at 02:05
Should have stated that the pID is auto_increment, so we know that the higher the number the later the date. — FLOG51, Aug 29 '14 at 03:10

Gordon Linoff · Accepted Answer · 2014-08-29T02:13:19.220

0

Hmmm. We can get the maximum id of the last not failed payment and then sum the failed payments with bigger ids. This basically does what you want:

select p.memid, count(*)
from payment p
where id > coalesce((select max(id) from payment p2 where p2.memid = p.memid and p2.pstatus = 1), 0)
group by p.memid;

But, it doesn't return the 0s, so let's turn this into a conditional aggregation:

select p.memid,
       sum(id > coalesce((select max(id) from payment p2 where p2.memid = p.memid and p2.pstatus = 1), 0)
          ) as numfails
from payment p
group by p.memid;

Here is a SQL Fiddle.

edited Aug 29 '14 at 02:13

answered Aug 29 '14 at 02:04

Gordon Linoff

1,242,037
58
646
786

Thanks, works perfectly! And the conditional aggregation also allows me to handle Pending status of 3. By changing your p2.pstatus = 1 to p2.pstatus <> 2. Great work. – FLOG51 Aug 29 '14 at 03:21

score 0 · Answer 2 · answered Aug 29 '14 at 02:19

There are several ways to obtain that result. Here's an example of one of those approaches, probably not the most efficient, but fairly easy to figure out what it's doing:

SELECT n.memID
     , COUNT(r.pStatus) AS failCount
  FROM ( SELECT m.memID
           FROM Payment m
          GROUP BY m.memID
       ) n 
  LEFT
  JOIN Payment r
    ON r.memID = n.memID
   AND r.pStatus = 2
   AND NOT EXISTS ( SELECT 1 
                      FROM Payment p
                     WHERE p.memID = r.memID
                       AND p.pStatus = 1
                       AND p.pID > r.pID
                  )
GROUP BY n.memID

The inline view n gets us the list of all memID from the Payment table; we could use some other source, like the table in which memID is the primary key, but we aren't given any information about that, so we generate a distinct list from the Payment table.

Next, we perform an outer join to rows in the Payment table that are status "failed". The "trick" we use is a NOT EXISTS predicate to test whether or note there's a more recent row in Payment that has a status of "Success". We only return a row if there isn't a more recent "success" row.

Then, we perform a GROUP BY operation to aggregate the rows, so we get a single row for each memID. And we get the count of failed payments by doing a COUNT() aggregate on a column from r we know will be non-null if we found a match.

This isn't the only way to obtain the result, there are several other approaches.

Mysql Counting most recent consecutive number rows that match for multiple id's

2 Answers2

Linked