bio bio - 5 months ago 8
SQL Question

MySQL get count of periods where date in row

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

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

Comments