Sumon Sumon - 24 days ago 12
MySQL Question

mysql query return only the value that has not change over time

I have a table value like

Id_indicator, Value , date_data
1 2 01/10/2016
1 2 03/10/2016
1 3 04/10/2016
1 2 05/10/2016
2 21 06/10/2016
2 21 07/10/2016
2 21 08/10/2016
3 3 09/10/2016
3 4 10/10/2016
3 4 11/10/2016
4 4 12/10/2016


I need to query that table and only need to get id_indicator and count(number or id_indicator)where value has not change over the time period.

I tried with group by query

select id_indicator, count(*), value
from shrewd_db.indicator_status_history
where date_data between '2016-10-01 00:00:00' and '2016-10-01 10:59:59'
group by id_indicator, value`;


now difficult to get only value that was unchanged like
id_indicator = 2
,
value =21
and
max (date_data) = 08/10/2016

Answer

SQL Fiddle Demo

I have to change the date range, otherwise only first row will be include it

SELECT `Id_indicator`, COUNT(*), max(value) as value
FROM Table1
WHERE  `date_data` between '2016-10-01 00:00:00' and '2016-10-13 10:59:59'
GROUP BY `Id_indicator`
HAVING max(value) = min(value)

OUTPUT

This result also will include indicator with only one record, where obviously cant change value. You may want add HAVING COUNT(*) > 1 to remove those

enter image description here