Getting count of foreign keys and joining to another list?

Question

I have two tables, lists and items

Lists table looks like this and

the items table looks like this

I am trying to query the database to get the below result,

| list       | count |
|------------|-------|
| my list    | 1     |
| my list 2  | 5     |

I could get the count with

SELECT count(items.list_id) as count
from items
group by list_id

when joining the list to this query, the counts are getting wrong. What can be the query to get the correct results? my database is sqlite.

Answer 1

looking to your sample seems you need a join between the tables

    SELECT list.name,  count(items.list_id) as count
    from list
    inner join  items on list.id = items.list_id 
    group by list.name