oracle - Performing arithmetic operation on certain rows and displaying the result -

below table contents:

select * summary_weekly_sales;  distributor    date_of_activation  number_of_sales -------------- ------------------  --------------- charan          25-apr-13              23 charan          26-apr-13               2 charan          28-apr-13               5 charan          29-apr-13              50 anil            25-apr-13              13 anil            26-apr-13               4 anil            28-apr-13               5 anil            29-apr-13              30 

in ireport date_of_activation input parameter (but here taking date_of_activation 29-apr-13), want output displayed below:

distributor    avg_sales_week   number_of_sales -------------- ---------------  --------------- charan          10              50  anil            7.33            30 

where,

avg_sales_week average week sales per distributor (i.e. 7 days of 29-apr-13)

i.e. charan distributor average = (5+2+23)/3

number_of_sales sales done on 29-apr-13

i tried wm_concat function of oracle not working expected.

is there way above expected result.

regards, charan

this it:

select distributor ,      sum(case when date_of_activation < date '2013-04-29'            number_of_sales end)          / count(distinct case when date_of_activation < date '2013-04-29'             date_of_activation end) avg_sales_week    ,      sum(case when date_of_activation=date '2013-04-29'             number_of_sales end) number_of_sales   summary_weekly_sales  date_of_activation between date '2013-04-29' - 7 , date '2013-04-29' group distributor;  distributo avg_sales_week number_of_sales ---------- -------------- --------------- anil           7.33333333              30 charan                 10              50 

just replace date '2013-04-29' parameter name e.g. p_date use in procedure.