CODEWITHSUNDEEP
databasepostgresql
lostDeveloper
lostDeveloper

Reputation: 63

Left join returning null instead of 0

So I'm trying to do something like this:

Ex Table:
id, Name, To
1,  A,    2
2,  B,    0
3,  C,    2
4,  D,    0
5,  E,    1

Expected results:
id, Name, To, Count
1,  A,    2,  1
2,  B,    0,  2
3,  C,    2,  0
4,  D,    0,  0
5,  E,    1,  0

Here I'm trying to get a count of rows being referenced each time. For example row with id 1 is referenced once in a row with id 5, similarly row with id 2 is referenced twice in rows with id 1 and id 3.

This is what I have tried:

select *
from sample
left join(
          select "to", count(*)
          from sample
          group by "to") j on j."to" = sample."id"

and this is what I'm getting:

Results:

id, Name, To, Count
1,  A,    1,  1
2,  B,    2,  2
3,  C,    null,null
4,  D,    null,null
5,  E,    null,null

Any ideas how I can make this work?

Upvotes: 0

Views: 24

Answers (1)

lostDeveloper
lostDeveloper

Reputation: 63

Found the solution. I ended up using a correlated query.

select *,
       (select count(to)
        from sample s
        where s.to = sample.id
       ) as ct
from sample;

Thanks to: Gordon Linoff's Answer

Upvotes: 1

Related Questions