CODEWITHSUNDEEP
mysqlsqlgroup-bygrouping
jonua
jonua

Reputation: 2005

How I can group results in the sql query by groups with interval?

I have the following data in a mysql table

|id|value|date|
|1|88|2015-08-26 08:00:00|
|2|88|2015-08-26 09:00:00|
|3|76|2015-08-26 10:00:00|
|4|76|2015-08-26 11:00:00|
|5|88|2015-08-26 12:00:00|
|6|76|2015-08-26 13:00:00|
|7|76|2015-08-26 14:00:00|
|8|76|2015-08-26 15:00:00|
|9|94|2015-08-26 16:00:00|
|10|94|2015-08-26 17:00:00|
|11|63|2015-08-26 18:00:00|
|12|94|2015-08-26 19:00:00|

I want to generate the following result:

|value|min date|max date|
|88|2015-08-26 08:00:00|2015-08-26 09:00:00|
|76|2015-08-26 10:00:00|2015-08-26 11:00:00|
|88|2015-08-26 12:00:00|2015-08-26 12:00:00|
|76|2015-08-26 13:00:00|2015-08-26 15:00:00|
|94|2015-08-26 16:00:00|2015-08-26 17:00:00|
|63|2015-08-26 18:00:00|2015-08-26 18:00:00|
|94|2015-08-26 19:00:00|2015-08-26 19:00:00|

I want to group the results that had the same value by 'date intervals'.

What is the query I need to write to achieve this result?

Upvotes: 2

Views: 222

Answers (2)

Drew
Drew

Reputation: 24949

Schema:

create table thing1
(   id int not null,
    value int not null,
    dt datetime not null
);
insert thing1 (id,value,dt) values
(1,88,'2015-08-26 08:00:00'),
(2,88,'2015-08-26 09:00:00'),
(3,76,'2015-08-26 10:00:00'),
(4,76,'2015-08-26 11:00:00'),
(5,88,'2015-08-26 12:00:00'),
(6,76,'2015-08-26 13:00:00'),
(7,76,'2015-08-26 14:00:00'),
(8,76,'2015-08-26 15:00:00'),
(9,94,'2015-08-26 16:00:00'),
(10,94,'2015-08-26 17:00:00'),
(11,63,'2015-08-26 18:00:00'),
(12,94,'2015-08-26 19:00:00');

Query:

set @grp := 0, @value:=0;
select value,min(dt) as min_date,max(dt) as max_date
from 
( 
  select id,value,dt,
      @grp := if(@value <> value, @grp + 1, @grp) as grouping,
      @value := value as drewisdummy
  from thing1
  order by id
) xxx
group by grouping

Results:

+-------+---------------------+---------------------+
| value | min_date            | max_date            |
+-------+---------------------+---------------------+
|    88 | 2015-08-26 08:00:00 | 2015-08-26 09:00:00 |
|    76 | 2015-08-26 10:00:00 | 2015-08-26 11:00:00 |
|    88 | 2015-08-26 12:00:00 | 2015-08-26 12:00:00 |
|    76 | 2015-08-26 13:00:00 | 2015-08-26 15:00:00 |
|    94 | 2015-08-26 16:00:00 | 2015-08-26 17:00:00 |
|    63 | 2015-08-26 18:00:00 | 2015-08-26 18:00:00 |
|    94 | 2015-08-26 19:00:00 | 2015-08-26 19:00:00 |
+-------+---------------------+---------------------+
7 rows in set (0.00 sec)

Upvotes: 3

Balinti
Balinti

Reputation: 1534

Try this:

SELECT value, min(date) AS 'min date', max(date) AS 'max date'
FROM yourTable
GROUP BY round((hour(date)/8)+1,0),value

It seems like your results are grouped by quarters of the day.

Upvotes: 1

Related Questions