3

Let's say I have such a table (ordered by date):

id | name | type  | date 
 1 | A    | 1     | 01-08-2012
 2 | A    | 2     | 01-08-2012
 3 | B    | 1     | 02-09-2012
 4 | A    | 1     | 01-10-2012
 5 | A    | 4     | 01-10-2012
 6 | A    | 5     | 02-10-2012

I want to group subsequent rows that have the same 'name' value and count them:

name | count 
A    | 2
B    | 1
A    | 3

I was thinking about writing a stored procedure and using cursors, but I was also wondering, if there's a simpler solution, for example using nested SELECTs, etc.

My question is very similar to: how to group array and count them, but that one concerns PHP.

Community
  • 1
  • 1
jacek.ciach
  • 904
  • 11
  • 20
  • what is the difference between `A | 2` and `A | 3`? – jcho360 Nov 26 '12 at 14:05
  • "A | 2" means that there are 2 subsequent rows with 'name' set to "A", "A | 3" means that there are 3 subsequent rows with 'name' set to "A"; between them there is one row with name = "B". – jacek.ciach Nov 26 '12 at 14:23

1 Answers1

1

To do that I used a couple of variables, the table structure, I created my own just for testing and it's:

create table abc (id int, name varchar(20),type int);

insert into abc values 
( 1 , 'A'    , 1  ),
( 2 , 'A'    , 2 ),
( 3 , 'B'    , 1  ),
( 4 , 'A'    , 1  ),
( 5 , 'A'    , 4  ),
( 6 , 'A'    , 5  )

the query ended being like this:

set @a:='';
set @counter:=1;
set @groupby:=0;
select *,count(REPEATED) from (select name,if(@a=name,@counter:=@counter+1,@counter:=1) as rep,if(@counter=1,@groupby:=@groupby+1,@groupby) as repeated,@a:=name type from abc) as t group by repeated

you can see it works in SQLFIDDLE if you have any question let me know.

In the SQLFIDDLE

jcho360
  • 3,724
  • 1
  • 15
  • 24
  • I like it and now I have a new view for my problem, but your solution gives wrong answer: the query says that B is 2 times and doesn't keep the order of 'name', so, even if the B's count was correct, I don't know which A is which. – jacek.ciach Nov 26 '12 at 16:40
  • I figured out, that a problem is with GROUP BY. It orders 'groups inside' in such a way, that the row with rep = 1 goes first in each group. – jacek.ciach Nov 26 '12 at 17:07
  • please try it again let me know – jcho360 Nov 26 '12 at 17:13
  • Oh, now works:). But I've just tried exactly the same way and have failed. I must have been mistaken something. – jacek.ciach Nov 26 '12 at 17:24