SQLite Count between two tables

Question

I'm not sure if this is possible in SQLite (for an Android app) as it may require and an INNER JOIN or OUTER JOIN which sqlite doea not support.

I'm trying to output the values of a JOIN between two tables that includes the count of fields in one table:

[Table1]
ID  Title
1   "Title 1"
2   "Title 2"
3   "Title 3"

[Table2]
ID PID Title      New
1  1   "Title 1a"  0
2  2   "Title 2a"  1
3  2   "Title 2b"  1
4  3   "Title 3a"  0
5  3   "Title 3b"  0

Desired output:
1  0  "Title 1"
2  2  "Title 2"
3  0  "Title 3"

What I have:

SELECT Table2.pid,count(Table2.pid),Table1.title
FROM Table1
JOIN Table2 
ON Table2.pid = Table1.id
WHERE Table2.new = 1
GROUP BY Table2.pid

Outputs:

2  2  "Title 2"

Answer 1

You can get the value you want by aggregation in a subquery:

select 
  (select count(*) from Table2 t2 where t2.pid = t1.id and t2.New = 1) counter,
  t1.Title
from
  Table1 t1 

See the demo

Another solution with group by:

select 
  count(t2.Title) counter, 
  t1.Title 
from Table1 t1
left join (
  select * from Table2 where new = 1
) t2
on t2.pid = t1.id
group by t1.Title

See the demo