Group data and count based on consecutive occurrence

Question

Tricky for me to define the problem, maybe you understand it through my dummy data.

I have this data:

Now I have to count TaskPK so, I make this query

Select PK, TaskPK, Count(*)
From tbl
Group by TaskPK

Iit brought this result

TaskPK, Count(*)
1, 4
2, 4
5, 3

But I want slight different result Like this

TaskPK, Count(*)
1, 2
2, 2
5, 1
1, 2
2, 2
5, 2

The above result based on consecutive data occurrence, as TaskPK start with 1 (it group together), then it change it 2 (it group together), then 5 (it group together) taskPK. But as TaskPK again shift to 1, then it should group seperatly not link with previous occurrence of 1, this task seperately count, is this possible?

I am working on mysql but it is pure based on sql, not mysql or sql-server. I just added these two tags so that question get more attention. is this bad? — Muhammad Faizan Khan, Jul 01 '17 at 07:17
I'm preparing query. In the mean time have a look on https://stackoverflow.com/questions/19541762/mysql-counting-the-consecutive-number-rows-that-match this thread. — MD Ruhul Amin, Jul 01 '17 at 07:17
Possible duplicate of [Mysql Counting the consecutive number rows that match](https://stackoverflow.com/questions/19541762/mysql-counting-the-consecutive-number-rows-that-match) — MD Ruhul Amin, Jul 01 '17 at 07:18
@MohammadFaizanKhan - Of course bad!! there are solutions which will work in Sql Server and not in Mysql and vice versa.. So tell one — Pரதீப், Jul 01 '17 at 07:20
@MohammadFaizanKhan - Example here is one way to do it using `Window` functions `LAG` and `SUM()OVER()` http://rextester.com/ORHUN52577 but this will not work in `Mysql` — Pரதீப், Jul 01 '17 at 07:29
@MohammadFaizanKhan - It is not going to be easy doing it in `Mysql` — Pரதீப், Jul 01 '17 at 07:33
@Prdp what about the above given links? i am checking that links — Muhammad Faizan Khan, Jul 01 '17 at 07:39
@Strawberry off course, i have made ORDER BY PK DESC for this reason. will use this. — Muhammad Faizan Khan, Jul 01 '17 at 07:56

score 0 · Answer 1 · answered Jul 01 '17 at 08:05

0

For Mysql - User variables to keep track of contiguous segments.Then group by this contiguous segments which are kept track by an incrementing counter,

SELECT MAX(TaskPK),COUNT(*)
FROM(SELECT PK,TaskPK,CASE WHEN  TaskPK=@TaskPK THEN @rn ELSE @rn:=@rn+1 END as g,
@TaskPK := TaskPK as task
FROM Test,(SELECT @rn:=0,@TaskPK:='')x
ORDER BY pk)z
GROUP BY g
ORDER BY pk;

See it working

answered Jul 01 '17 at 08:05

Mihai

26,325
7
66
81

Hello, I tried running the code but got this error: `Error Code: 1055. Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'z.PK' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – NothingBox Oct 14 '20 at 04:02

Group data and count based on consecutive occurrence

1 Answers1