CODEWITHSUNDEEP
mysqlsqljoin
Eckersley
Eckersley

Reputation: 89

Check if ID from a table exists in another table, and if so, how many times

Let's say I have two tables

Table a

some_ID
1
2
3
4

Table b

some_ID
1
2
1
4

Now what I would like to receive is a table like

id amount

1 | 2
2 | 1

I tried with a following query:

SELECT COUNT(a.some_id) as id  
            FROM Table_a  
            INNER JOIN Table_b  
            ON Table_a.some_id = Table.b.some_id

but that only returned how many id rows there are in both tables.
Any help?

Upvotes: 0

Views: 875

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112259

If you want the zero counts:

SELECT a.some_id AS id, count(b.some_id) as amount
FROM a LEFT JOIN b ON a.some_id = b.some_id
GROUP BY a.some_id

Result: 

id | amount  
 1 |  2  
 2 |  1  
 3 |  0  
 4 |  1

If not:

SELECT a.some_id AS id, count(*) as amount
FROM a INNER JOIN b ON a.some_id = b.some_id
GROUP BY a.some_id

Result: 

id | amount  
 1 |  2  
 2 |  1  
 4 |  1

The difference is the join type. Once left outer join. Then inner join. Note that in the first case it is important to count with count(b.some_id). With count(*) the rows with missing b entries would be counted as 1. count(*) counts the rows. count(expression) counts the non-null values.

Upvotes: 2

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Do the grouping on table_b and then join that result set on table_a

SELECT b.* FROM 
(
  SELECT id, COUNT(*) AS Cnt
  FROM Table_b
  GROUP BY id
) b
INNER JOIN Table_a a ON a.id = b.id

SQLFiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If I understand correctly, you want a histogram of histograms:

select cnt, count(*) as num_ids
from (select id, count(*) as cnt
      from b
      group by id
     ) b
group by cnt;

Upvotes: 1

Related Questions