Performing arithmetic operation on certain rows and displaying the result

Below is my table contents:

select * from 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 the ireport DATE_OF_ACTIVATION is the input parameter (but here I am taking date_of_activation as 29-APR-13), I want the output to displayed as below:

DISTRIBUTOR    avg_sales_week   NUMBER_OF_SALES
-------------- ---------------  ---------------
charan          10              50

anil            7.33            30

where,

avg_sales_week is the average week sales per distributor (i.e. 7 days back of 29-APR-13)

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

Number_Of_Sales is the sales done on 29-APR-13

I tried with wm_concat function of oracle but its not working as expected.

Is there any way to get the above expected result.

Regards, Charan

Answers


This would do it:

select distributor
,      sum(case when date_of_activation < date '2013-04-29'
           then number_of_sales end)  
       / count(distinct case when date_of_activation < date '2013-04-29' 
           then date_of_activation end) as avg_sales_week   
,      sum(case when date_of_activation=date '2013-04-29' 
           then number_of_sales end) number_of_sales
from   summary_weekly_sales
where  date_of_activation between date '2013-04-29' - 7 and date '2013-04-29'
group by distributor;

DISTRIBUTO AVG_SALES_WEEK NUMBER_OF_SALES
---------- -------------- ---------------
anil           7.33333333              30
charan                 10              50

Just replace date '2013-04-29' by your parameter name e.g. p_date to use in procedure.


Need Your Help

Can Warp in yesod used to host CGI application?

yesod warp

An old python CGI application is now running under ocsigen with cgimod extension. Whether or not warp support CGI application just by some configuration ?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.