I’m trying to write a query using the data below. My challenge is for each day, I need to count the number of times “105” appears in Col A and “100” appears in Col B. I do not need to count each instance “105” appears but rather each minute “105” appears. Likewise with Col B and “100”
Date Time A B 1/20/2021 1:06:12 AM 105 100 1/20/2021 1:06:16 AM 105 100 1/20/2021 1:06:20 AM 105 100 1/20/2021 2:28:12 AM 105 100 1/20/2021 2:28:16 AM 105 100 1/20/2021 2:28:20 AM 105 100 1/20/2021 3:50:08 AM 105 10 1/20/2021 3:50:12 AM 105 10 1/20/2021 3:50:16 AM 105 10 1/20/2021 5:12:04 AM 105 10 1/20/2021 5:12:08 AM 105 10 1/20/2021 5:12:12 AM 105 10
Detail:
Time A B 1:06 1 1 2:28 1 1 3:50 1 0 5:12 1 0
Query Result:
Date A B 1/21/20 4 2
Any suggestions on how to do this would be greatly appreciated.
Advertisement
Answer
One method is to use distinct on each column separately. Then aggregate:
select date, sum(a), sum(b)
from (select distinct date, hour(time) as hh, minute(time) as mm, 1 as a, 0 as b
from t
where a = 105
union all
select distinct date, hour(time) as hh, minute(time) as mm, 0 as a, 1 as b
from t
where b = 100
) t
group by date;