I have the following data in a MySQL database:
id value
-- -----
1 7.07
2 1.00
3 4.81
4 1.39
5 1.60
6 9.11
7 6.99
8 1.95
9 1.00
10 1.00
11 1.62
12 1.38
13 1.33
14 1.41
15 3.67
16 3.62
17 2.43
18 3.15
19 57.30
20 1.33
21 1.68
22 6.52
23 2.75
24 2.36
25 2.01
26 2.22
27 5.35
28 2.30
29 1.05
30 24.21
I'm trying to order the results, counting how many consecutive values within the same range are, just like this:
value consecutive repetitions
----- -----------------------
7. 1
1. 1
4. 1
1. 2
9. 1
6. 1
1. 7
3. 2
2. 1
3. 1
57. 1
1. 2
6. 1
2. 4
5. 1
2. 1
1. 1
24. 1
So, as you can see, I basically want to remove the float, grouping the numbers by it's integer value, and count how many consecutive repetitions they have.
I've tried this, but it doesn't seems to be working on MySQL:
SELECT value, COUNT(*)
FROM (select live_records.*,
(row_number() over (order by id) -
row_number() over (partition by value order by id)
) as grp
from live_records
) live_records
group by grp, value;
Any ideas? Thanks!
Table Structure:
create table test (a int, b float);
Inserted Values:
insert into test values(1,7.07);
insert into test values(2,1.00);
insert into test values(3,4.81);
insert into test values(4,1.39);
insert into test values(5,1.60);
insert into test values(6,9.11);
insert into test values(7,6.99);
insert into test values(8,1.95);
insert into test values(9,1.00);
insert into test values(10,1.00);
insert into test values(11,1.62);
insert into test values(12,1.38);
insert into test values(13,1.33);
insert into test values(14,1.41);
insert into test values(15,3.67);
insert into test values(16,3.62);
insert into test values(17,2.43);
insert into test values(18,3.15);
insert into test values(19,57.30);
insert into test values(20,1.33);
insert into test values(21,1.68);
insert into test values(22,6.52);
insert into test values(23,2.75);
insert into test values(24,2.36);
insert into test values(25,2.01);
insert into test values(26,2.22);
insert into test values(27,5.35);
insert into test values(28,2.30);
insert into test values(29,1.05);
insert into test values(30,24.21);
SQL Query:
set @groupvalue := 0;
set @comparevalue := null;
select groupingvalue, max(value), count(value)
from
(
select if(@comparevalue=floor(b),@groupvalue,@groupvalue := @groupvalue + 1 )
groupingvalue,
@comparevalue := floor(b) as value
from test t
) a
group by groupingvalue
Explanation: