CODEWITHSUNDEEP
androidsqliteormlite
John Ernest Guadalupe
John Ernest Guadalupe

Reputation: 6629

How to count occurrences of a foreign table id in a certain table in SQLite?

So I have the following table: 

Table A
a_id    b_id

Table B
b_id    b_name

I want to count how many times the id in table B is shown or used as a value in the b_id column in Table A. Presumably, the result should be something as such: 

ID     count     name
1      10        aaaa
2      8         bbbb
3      11        cccc

I considered using the query below:

Select b_id from table_b

to get all the ids. Then iterate over each one of them and count them like this : 

Select count(*) from table_a where b_id = ''

But the process is just too long. I want to make it a little shorter in within one query. But I don't know how to iterate over each rows using SQLite language.

(Even just a push to the right direction will help. )

Upvotes: 1

Views: 84

Answers (2)

CL.
CL.

Reputation: 180192

This can be done with a correlated subquery:

SELECT b_id AS ID,
       (SELECT COUNT(*)
        FROM TableA
        WHERE TableA.b_id = TableB.b_id
       ) AS count,
       b_name AS name
FROM TableB;

Upvotes: 1

Kushal
Kushal

Reputation: 8508

The following sql query should work :

   SELECT      a.a_id, 
               COUNT(b.b_id)    
   FROM        Table_A a
   JOIN  Table_B b on a.b_id=b.b_id
   GROUP BY    a.a_id

The above is working :

enter image description here

Is it your requirement?

Upvotes: 2

Related Questions