How to get the count of detail rows in a master/detail SQL SELECT query and subquery

As a brief note to self, I just used this SQL SELECT query to show both (a) master/summary information about each URL row in the urls database table, and also (b) detail information in the form of the number of rows in the url_clicks database table for each row in the urls table:

select u.id, u.long_url, u.short_uri, u.notes, u.created,
(select count(*) from url_clicks where url_clicks.url_id = u.id) as num_clicks
from urls u
order by u.created desc

As shown, the way I did that was with a SQL sub-query within the main SELECT query. With this query the resulting output looks like this:

1, https://alvinalexander.com, aa,  4, theDate
2, http://onemansalaska.com,   oma, 5, theDate

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.