enormousson
Programmer
Hi I am looking for help with an aggregate function
eg. two tables
table 1
id name
1 bob
2 rob
3 dave
table 2
id name link_id
1 smith 3
2 jones 2
3 slater 1
4 green 2
5 baker 3
I want to create a recordset that contains each of the fields in table 1 as well as the count of the number of records in table2 that match on the link id -
i.e.
(something like)
select id,
name,
COUNT(* from table2 WHERE table1.id = table2.linkid) as total
from table1, table2
returning (in this example)
id name total
1 bob 2
2 rob 2
3 dave 1
I can't seem to construct the query correctly - can anyone help ?
thanks
eg. two tables
table 1
id name
1 bob
2 rob
3 dave
table 2
id name link_id
1 smith 3
2 jones 2
3 slater 1
4 green 2
5 baker 3
I want to create a recordset that contains each of the fields in table 1 as well as the count of the number of records in table2 that match on the link id -
i.e.
(something like)
select id,
name,
COUNT(* from table2 WHERE table1.id = table2.linkid) as total
from table1, table2
returning (in this example)
id name total
1 bob 2
2 rob 2
3 dave 1
I can't seem to construct the query correctly - can anyone help ?
thanks