Count of consecutive not null values

Question

The count should be 3 and 1 in the following query. The count should be of the points earned consecutively. So once the user fails to earn any points, the count restarts.

mysql> select name, count(*) from sortest group by name, (points = 0) OR (points is NULL)  having name= 'john';
+------+----------+
| name | count(*) |
+------+----------+
| john |        4 |
| john |        2 |
+------+----------+
2 rows in set (0.00 sec)

mysql> select * from sortest;
+----+------+--------+
| id | name | points |
+----+------+--------+
|  1 | john |     12 |
|  2 | john |     23 |
|  3 | john |     43 |
|  4 | hari |   NULL |
|  5 | hari |     56 |
|  6 | john |   NULL |
|  7 | hari |      0 |
|  8 | john |     44 |
|  9 | john |      0 |
| 10 | hari |     43 |
| 11 | hari |     44 |
| 12 | hari |     78 |
| 13 | hari |      0 |
+----+------+--------+
13 rows in set (0.00 sec)

mysql> show create table sortest\G
*************************** 1. row ***************************
       Table: sortest
Create Table: CREATE TABLE `sortest` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) default NULL,
  `points` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

In this case, john's count should be 5, 1 and 2

5 and 1 because it ends with 0 or NULL and 2 because it has not yet closed with the 0 or NULL

mysql> select * from sortest;
+----+------+--------+
| id | name | points |
+----+------+--------+
|  1 | john |     12 |
|  2 | john |     23 |
|  3 | john |     43 |
|  4 | hari |   NULL |
|  5 | hari |     56 |
|  6 | john |   NULL |
|  7 | hari |      0 |
|  8 | john |     44 |
|  9 | john |      0 |
| 10 | hari |     43 |
| 11 | hari |     44 |
| 12 | hari |     78 |
| 13 | hari |      0 |
| 14 | john |     55 |
| 15 | john |     95 |
+----+------+--------+
15 rows in set (0.00 sec)

mysql> select name, count(*) from sortest group by name, (points = 0) OR (points is NULL)  having name= 'john';
+------+----------+
| name | count(*) |
+------+----------+
| john |        6 |
| john |        2 |
+------+----------+
2 rows in set (0.00 sec)

Answer 1

Why should the counts be 3 and 1? Your query groups by:

1. name
2. (points = 0 OR points IS NULL)

There are two rows where name=john and (points = 0 OR points IS NULL) and there are four other rows where name=john.

So, your query is working just as you've described it. What is it you're ultimately trying to accomplish?

Answer 2

Firstly, what you're asking is a bit of a non-sequitur. You see, there's no semblence of order without an order by clause. But, my assumption is that you want to get counts of everything before a null row, ordered by id.

Secondly, here goes:

select
    a.name,
    (select count(*) from sortest where id < a.id and
        id > b.priorId) as count
from
    sortest a
    inner join (select id,
                    (select coalesce(max(id), 0) 
                     from sortest where points is null and id < s1.id) as priorId
                from sortest s1 where points is null
                union
                select max(id) as id,
                    (select coalesce(max(id), 0) 
                     from sortest where points is null and id < s1.id) as priorId
                from sortest s2group by name) b on
        a.id = b.id

It's a bit messy and convoluted, but it'll get you what you want.

Answer 3

To select the max winning streak:

SELECT  name, MAX(cnt)
FROM    (
        SELECT  name, COUNT(*) AS cnt
        FROM    (
                SELECT  sortest.*,
                        @r := @r + ((COALESCE(@name, name) <> name) OR (COALESCE(points, 0) = 0)) AS series,
                        @name := name
                FROM    (
                        SELECT  @name := NULL,
                                @r := 0
                        ) vars, sortest
                ORDER BY
                        name, id
                ) q
        WHERE   points > 0
        GROUP BY
                name, series
        ) q2
GROUP BY
        name

To select all winning streaks:

SELECT  name, COUNT(*) AS cnt
FROM    (
        SELECT  sortest.*,
                @r := @r + ((COALESCE(@name, name) <> name) OR (COALESCE(points, 0) = 0)) AS series,
                @name := name
        FROM    (
                SELECT  @name := NULL,
                        @r := 0
                ) vars, sortest
        ORDER BY
                name, id
        ) q
WHERE   points > 0
GROUP BY
        name, series