Q:

Select Count between tables

I'm brand new to SQL, and a little lost on using SELECT. I have two tables: table1 has two columns with a name and unique integer identifier (e.x. 'Dave', 998); table2 has one relevant column, which repeats that same identifier (multiple instances of 998).
I want to run a query to go through every row in table1 and count / SELECTevery instance of that unique integer in table 2. (e.g. if there are 3 instances of Dave's 998 number, I want SELECT to output column1: 'Dave', column2: 3) This would be the equivalent elsewhere of a simple CountIf, which might be part of my problem--I'm stuck on Python and still struggling to adjust.

A:

A simple join/count query should work here:
SELECT
    t1.name,
    t1.id,
    COUNT(t2.id) AS cnt
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id
GROUP BY
    t1.name,
    t1.id;

Note that we left join and count table2.id here, because in the case where an id in the first table matches to nothing, it would correctly report a count of zero.

