CODEWITHSUNDEEP
mysqlsql
ThoureaulyRekt
ThoureaulyRekt

Reputation: 97

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.

Upvotes: 1

Views: 49

Answers (2)

Amith Kumar
Amith Kumar

Reputation: 4882

You will need two basic but powerful concepts of SQL to get your desired result. No matter what database engine and what version, its inherently supported in all relational databases afaik): JOIN & Aggregation(count with group by) .

select t1.name, count(t2.id)
from table1 t1
join table2 t2
on t1.id = t2.id
group by t1.name;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522284

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.

Upvotes: 2

Related Questions