CODEWITHSUNDEEP
sqlselectcount
rickyalbert
rickyalbert

Reputation: 2652

Sql - Simple query with count

I have a simple question on how to read the number of occurences of some values in a table B that references to a value in a table A. It's all explained better in the following example:

Let's say I have two simple tables, A with an attribute ID and B with an attribute ID that references A.ID; I use this code to find the number of occurences of a A.ID value into B:

SELECT A.ID, COUNT(*)
FROM A JOIN B ON A.ID = B.ID
GROUP BY A.ID

Is it possible to achieve the same result using something like the following code...

SELECT ID, -- SOMETHING --
FROM A
....

... using this subquery?

SELECT COUNT(*)
FROM B WHERE B.ID = A.ID

Thank you very much

Upvotes: 0

Views: 34

Answers (1)

bgoldst
bgoldst

Reputation: 35314

I think you might be referring to a correlated subquery:

select a.id, (select count(1) from b where id=a.id) cnt from a;

The a.id term in the subquery binds with table a in the outer query.

Upvotes: 2

Related Questions