Access Query to sum by day and count by hour / minute

Question

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.

Answer 1

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;