# Count for all existing combinations

So I have a table, Let’s say:

```Val1 | Val2 | StartDate | EndDate
1   |  1   | 31-12-2019| 1-1-2018
1   |  1   | 1-1-2019  | 1-1-2019
1   |  1   | 1-1-2019  | 2-1-2019
2   |  1   | 1-1-2019  | 1-1-2019
1   |  2   | 2-1-2019  | 2-1-2019
1   |  1   | 2-1-2019  | 3-1-2019
```

And I want to count all combinations that started or ended or started and ended on specific day, let’s say 1-1-2019 in this case. So the desired output would be:

```Val1 | Val2 | Count
1   |  1   |   3 (one started, one ended and one started and ended on 1-1-2019)
1   |  2   |   0 (one row that started next day, so it's not counted)
2   |  1   |   1 (one row started and ended on that day)
2   |  2   |   0 (row with combination of those two values doesn't exist)
```

However my current query doesnt’t do that.

My attempt:

```select ac.val1, ac.val2, count(ac.val1) as cnt
from (select distinct val1 from bla) ac1 cross join
(select distinct val2 from bla) ac2 left join
bla ac
on ac.val1 = ac1.val1 and ac.val2 = ac2.val2
where (startdate<'2019-1-1' and enddate='2019-1-1') or
(startdate='2019-1-1' and enddate = '2019-1-1') or
(startdate='2019-1-1' and enddate>'2019-1-1')
group by ac.val1, ac.val2;
```

It only correctly shows:

```Val1 | Val2 | cnt
1   |  1   |  3
2   |  1   |  1
```

Your conditions in the `WHERE` clause must be moved to the `ON` clause, otherwise your `LEFT JOIN` becomes an `INNER JOIN`.
Also, why so complicated conditions?
Isn’t `'2019-01-01' in (startdate, enddate)` enough?
Try this:

```select ac1.val1, ac2.val2, count(ac.val1) as cnt
from (select distinct val1 from bla) ac1 cross join
(select distinct val2 from bla) ac2
left join bla ac
on ac.val1 = ac1.val1 and ac.val2 = ac2.val2 and '2019-01-01' in (startdate, enddate)
group by ac1.val1, ac2.val2;
```

See the demo.
Results:

```| val1 | val2 | cnt |
| ---- | ---- | --- |
| 1    | 1    | 3   |
| 1    | 2    | 0   |
| 2    | 1    | 1   |
| 2    | 2    | 0   |
```