Join a columns count from another table

Question

I have 2 tables : urls and links.

urls
- urls.url

links
- links.source
- links.destination

I'm trying to select all the urls and add count of incoming and outgoing URLs, result should be :

URL; Count Incoming links; Count Outgoing links

I have tried the following to get the incoming URLs which sadly isn't valid SQL, and at this point I don't know how to look for the solution to this problem.

SELECT * FROM urls JOIN COUNT(links.source) ON urls.url = links.source 

Answer 1

You can use correlated subqueries:

select u.url,
    (select count(*) from links l where l.source      = u.url) as incoming_links,
    (select count(*) from links l where l.destination = u.url) as outgoing_links
from urls u

Alternatively, you can do conditional aggregation:

select u.url,
    sum(case when u.url = l.source      then 1 else 0 end) as incoming_links,
    sum(case when u.url = l.destination then 1 else 0 end) as outgoing_links 
from urls u
left join links l on u.url in (l.source, l.destination)
group by u.url

Depending on your database, there might be a neater way to express the conditional sum. For example, in MySQL:

sum(u.url = l.source) as incoming_links

Or in Postgres:

count(*) filter(where u.url = l.source) as incoming_links