CODEWITHSUNDEEP
sqlcount
D. Studer
D. Studer

Reputation: 1875

sql: count number of appearances in other table

I have two tables tab1 and tab2.

tab1:

id | name | var1 | ...
-------------------------
1  | a    | ...  | ...
2  | b    | ...  | ...
3  | c    | ...  | ...
4  | d    | ...  | ...


tab2:

id | name | var1 | ...
-------------------------
1  | a    | ...  | ...
2  | a    | ...  | ...
3  | a    | ...  | ...
4  | b    | ...  | ...
5  | b    | ...  | ...

I'd like to get all the entries in tab1 plus as an additional column appearances containing the number of appearances of each value of tab1.name in tab2.

I tried something like this with a subquery but could not get it to work:

SELECT 
  *,
  (SELECT * FROM tab2 ??? ) AS `appearances`
FROM
  tab1

Upvotes: 1

Views: 1145

Answers (3)

Farid Imranov
Farid Imranov

Reputation: 2075

You can do this by querying count of each appearance by name like this:

SELECT 
  t1.*,
  (SELECT count(*) FROM tab2 where name = t1.name ) AS `appearances`
FROM
 tab1 t1;

Upvotes: 2

Rumpelstinsk
Rumpelstinsk

Reputation: 3241

Take a look to GroupBy and Count

Select a.Id, a.Name, Count(b.Id) as appereances
FROM tab1 a
    LEFT JOIN tab2 b on a.Name = b.Name
GROUP BY a.Id, a.Name

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

A left join combined with an aggregation should work here:

SELECT
    t1.id,
    t1.name,
    t1.var1,
    COUNT(t2.name) AS num_occurrences
FROM tab1 t1
LEFT JOIN tab2 t2
    ON t1.name = t2.name
GROUP BY
    t1.id,
    t1.name,
    t1.var1;

Note that depending on your database you might be able to just GROUP BY the id column of the first table (the other columns in the select clause being functionally dependent on the id). If you don't like listing many columns in GROUP BY, you could also aggregate the second table separately in a subquery, and then join to that:

SELECT
    t1.id,
    t1.name,
    t1.var1,
    COALESCE(t2.cnt, 0) AS num_occurrences
FROM tab1 t1
LEFT JOIN
(
    SELECT name, COUNT(*) AS cnt
    FROM tab2
    GROUP BY name
) t2
    ON t1.name = t2.name;

Upvotes: 3

Related Questions