CODEWITHSUNDEEP
mysqlsqljoin
Ritvik Joshi
Ritvik Joshi

Reputation: 95

Joining tables and getting a count of specific record

I am having two tables say table_a and table_b with following structure.

table_a : ID(primary key), value_one

table_b: ID, value_two

Note that id in table_b is not primary and contains multiple records to same id.

now i want a third table which displays a record for every id in table_a with columns being

id column_count

the column _count will display number of records (count) in table_b with value_two = 'c'. and i want to iterate this to all records of table_a.

For example lets say our table_a is like this:

id   value_one
1   20
2   40
3   50

table_b

id value_two
1   10
1   20
1   10
2   40
2   10
3   40
3   10

I want records with value_two = 10 so my new table would look like

id   count
1   2
2   1
3   1

Since id 1 has two records with value_two = 10 and id 2 and id 3 have one record each with value_two = 10

Upvotes: 0

Views: 110

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

One way of doing this uses a correlated subquery:

select a.id,
       (select count(*) from table_b b where b.id = a.id and b.value_two = 10) as cnt_10
from table_a a;

Another method uses a left join:

select a.id, count(b.id)
from table_a a left join
     table_b b
     on b.id = a.id and b.value_two = 10
group by a.id;

In your example data, this works:

select b.id, count(*)
from table_b b
where b.value_two = 10
group by b.id;

This is equivalent under the following circumstances:

  • All ids in a are in b.
  • All ids have at least one value of 10.

If these two conditions are true, then use this simpler query.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do conditional aggregation :

select id, sum(value_two = 10) as count
from table_b tb
group by id;

If you want matching ids then add INNER JOIN. This will show 0 count whereas value_two = 10 not found. You can add where clause to find only value_two = 10 count.

Upvotes: 1

Related Questions