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

#1

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

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

#2

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

#3

@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;

#4

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

#5

#6

thank you, this also works