MySQL get count of periods where date in row

Question

I have an MySQL table, similar to this example:

c_id    date    value
66  2015-07-01  1
66  2015-07-02  777
66  2015-08-01  33
66  2015-08-20  200
66  2015-08-21  11
66  2015-09-14  202
66  2015-09-15  204
66  2015-09-16  23
66  2015-09-17  0
66  2015-09-18  231

What I need to get is count of periods where dates are in row. I don't have fixed start or end date, there can be any.

For example: 2015-07-01 - 2015-07-02 is one priod, 2015-08-01 is second period, 2015-08-20 - 2015-08-21 is third period and 2015-09-14 - 2015-09-18 as fourth period. So in this example there is four periods.

SELECT 
SUM(value) as value_sum,
... as period_count
FROM my_table 
WHERE cid = 66

Cant figure this out all day long.. Thx.

Answer 1

I don't have enough reputation to comment to the above answer.

If all you need is the NUMBER of splits, then you can simply reword your question: "How many entries have a date D, such that the date D - 1 DAY does not have an entry?"

In which case, this is all you need:

SELECT 
    COUNT(*) as PeriodCount
FROM
    `periods`
WHERE
    DATE_ADD(`date`, INTERVAL - 1 DAY) NOT IN (SELECT `date` from `periods`);

In your PHP, just select the "PeriodCount" column from the first row.

You had me working on some crazy stored procedure approach until that clarification :P