sql - Counting number of positive value in a query -

sql - Counting number of positive value in a query -

i'm working on next query , table

select dd.actual_date, dd.week_number_overall, sf.branch_id, avg(sf.overtarget_qnt) targetreach sales_fact sf, date_dim dd dd.date_id = sf.date_id , dd.week_number_overall between 88-2 , 88 , sf.branch_id = 1 grouping dd.actual_date, branch_id, dd.week_number_overall order dd.actual_date asc; actual_date week_number_overall branch_id targetreach ----------- ------------------- ---------- ----------- 13/08/14 86 1 -11 14/08/14 86 1 12 15/08/14 86 1 11.8 16/08/14 86 1 1.4 17/08/14 86 1 -0.2 19/08/14 86 1 7.2 20/08/14 87 1 16.6 21/08/14 87 1 -1.4 22/08/14 87 1 14.4 23/08/14 87 1 2.8 24/08/14 87 1 18 26/08/14 87 1 13.4 27/08/14 88 1 -1.8 28/08/14 88 1 10.6 29/08/14 88 1 7.2 30/08/14 88 1 14 31/08/14 88 1 9.6 02/09/14 88 1 -3.2

the "targetreach" column shows whether target has been reach or not. negative value means target wasn't reached on day. how can calculate number of row positive value query?

that show like:

total_positive_target_reach week_number_overall --------------------------- ------------------ 13 88

i have tried utilize case still not working right. lot.

you want utilize conditional aggregation:

with t ( <your query here> ) select week_number_overall, sum(case when targetreach > 0 1 else 0 end) t grouping week_number_overall;

however, rewrite original query utilize proper join syntax. query like:

select week_number_overall, sum(case when targetreach > 0 1 else 0 end) (select dd.actual_date, dd.week_number_overall, sf.branch_id, avg(sf.overtarget_qnt) targetreach sales_fact sf bring together date_dim dd on dd.date_id = sf.date_id dd.week_number_overall between 88-2 , 88 , sf.branch_id = 1 grouping dd.actual_date, branch_id, dd.week_number_overall ) t grouping week_number_overall order week_number_overall;

the difference between cte (the first solution) , subquery (in case) matter of preference.

sql oracle