Count how many times a value from a column it is found in a column from another table based on some criterias

hello, I am a kind of new to sql and I am struggling to automatize a report.
I have 2 tables. I need to fill the Count column in table1 based on tabl2
image

i need to count every ID from table1 to see how many times appears in table 2 but based on some criterias:

  • the value from table2 mut be >50000 and
  • the start date from table 1 must be <= than the date from table2

for instance the ID=7264451 from table 1 it repeats 4 times in table 2 but only 2 times matches the criterias mentioned above.

I only could count how many times the ID repeats in table2:
select table1 ID, count(table2 ID) as count
from table1 left join table2
on table1 ID=table2 ID
group by table1 ID

i have no idea how to put those clauses. It would be great if someone could help or give me a hint.
thank you

My first try will be something like this:

select table1 ID, count(table2 ID) as count
from table1 left join table2
on table1 ID=table2 ID
and table2.value > 50000
and table1.[start date]<=table2.[date]
group by table1 ID
1 Like

@stepson's method is what I would normally use as well, but more often I find myself using the APPLY operator because it seems more readable to me.

SELECT
	t1.ID,
	t1.StartDate,
	t2.[Count]
FROM
	Table1 t1
	OUTER APPLY
    (
		SELECT COUNT(*) AS [Count]
		FROM
			Table2 t2
		WHERE
			t1.Id = t2.Id
			AND t2.[value] > 50000
			AND t1.StartDate <= t2.Date
	) AS t2;
2 Likes

@JamesK, I also tend to use often the APPLY operator , for the same reason. :slight_smile:

thank you so much, it works :slight_smile: i will have to learn more about APPLY :smiley:

thank you, this also works :smiley: