Clever Geek Handbook

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


You can use a clause having

with distinct

count:



select `id`
from `table1`
where `year` between '1970' and '1972'
group by id
having count(distinct `year`) = 3
+3


source


Do you expect this?

select id, count(*)
from table1
WHERE (year BETWEEN '1970' AND '1972')
group by id
having count(distinct year) = 3
+1


source






More articles:


All Articles