SQLite count in left join request -

my model 1 wine can have many bottles. these bottles have field output_type null or not null.

actually, can count bottles this:

select count(distinct(bottles.id)) total_bottles wines left join bottles on wines.id = bottles.wine_id , bottles.status=1 , bottles.output_type null 

this works well.

but need more: want count bottles stored (output_type null) , count bottles gone (output_type not null) in same request.

so left join not enough because can't count bottles stored , bottles gone in main request.

i have seen this, including "sub request" in left join:

left join (select count(*) bottle_stored bottles bottles.status=1 , bottles.output_type null) total_stored on wines.id = bottles.wine_id 

but not working. if working, can duplicate left join count gone bottles.

edit: more explanations here. current request. can count bottles stored (output_type null) each wine.

select wines.id, wines.winery count(distinct(bottles.id)) total_bottles wines    left join bottles on wines.id = bottles.wine_id , bottles.status=1 , bottles.output_type null  wines.status = 1   group wines.id   

edit 2 (db schema added):

wines table ----------- id (vc) winery (vc) name (vc) status (int) ...  bottles table ------------- id (vc) wine_id (vc) input_type (int) input_date (datetime) output_type (int) output_date (datetime) status (int) ... 

i want:

• to list wines
• to count bottles stored (output_type null) each wine of list
• to count bottle gone (output_type not null) each wine of list

in same request.

my primary , foreign keys varchar because it's uuid (global sync system).

try this:

select count(distinct(bottles.id)) bottles_stored,         (select count(distinct(bottles.id)) total_bottles           wines           left join bottles on wines.id = bottles.wine_id , bottles.status = 1) - count(distinct(bottles.id)) bottles_gone   wines   left join bottles on wines.id = bottles.wine_id , bottles.status = 1 , bottles.output_type null 

1. select bottles stored => bottles.output_type = null
2. subquery counting total amount of bottles
3. subtract bottles_stored total_bottles => bottles gone

update after second edit of muqaddar:

first, query list wines (1):

select w.id,        w.name,        w.winery   wines 

next, count bottles stored (2) , bottles gone (2) specific wine id = 1:

select count(b.id)    bottles b   b.output_type null ,         b.wine_id = 1 

and

select count(b.id)    bottles b   b.output_type not null ,         b.wine_id = 1 

as third step combine of 3 queries query (2) , (3) subqueries , replace static id = 1 w.id outer query:

select w.id,        w.name,        w.winery,        (select count(b.id)            bottles b           b.output_type null ,                 b.wine_id = w.id) bottles_stored,        (select count(b.id)            bottles b           b.output_type not null ,                 b.wine_id = w.id) bottles_gone   wines