Check if the instances were minimum once, every year in a specific range
In MySQL, I was assigned a large dataset with data from 1970 to 2010. I want to test consistency: check if each instance runs once a year. I took a snippet from 1970-1972. As an example to demonstrate your problem.
input:
id year counts
-- ---- ---------
1 1970 1
1 1971 1
2 1970 3
2 1971 8
2 1972 1
3 1970 4
expected:
id 1970-1972
-- ----------
1 no
2 yes
3 no
Although I count within the date range, and then take out those with 3 counts: 1970, 1971, 1972. However, the following query does not force me to check every point in the range.
select id, count(*)
from table1
WHERE (year BETWEEN '1970' AND '1972') AND `no_counts` >= 1
group by id
What to do?
+3
source to share
3 answers
You can use GROUP BY
with CASE
/ inline if
.
Use CASE
. SQL Fiddle
select id,CASE WHEN COUNT(distinct year) = 3 THEN 'yes'ELSE 'No' END "1970-72"
from abc
WHERE year between 1970 and 1972
GROUP BY id
Using inline if
. SQL Fiddle
select id,IF( COUNT(distinct year) = 3,'yes','No') "1970-72"
from abc
WHERE year between 1970 and 1972
GROUP BY id
+3
source to share