All Forums Database
vijay65 17 posts Joined 10/13
26 Nov 2013
sum of amout for the date falling in the respective month i.e months wise sum amount is required

i have 1  table where we have amount column and calender date column with date.
other table with sales where i have order date when order is received ffrom customer.
 
Sum of of amount  for which the order Date is in between the last day and 1st day of that oder date.
for example we have 5 order date for current month then sum amount of 5 is needed.
same for the 6 order date in oct month then  sum amount of 6 is needed.
same for other month too/

thanks in advance

M.Saeed Khurram 544 posts Joined 09/12
26 Nov 2013

Try this

SELECT SUM(sales) AS Monthly_Sales
FROM Sales
WHERE salesdate BETWEEN Start_Date AND END_Date;

You can replace the start and end date with start and end date of the period for which you want to calculate the sum.
If you have a requirement to dynamically get sum of sales by month then we will have to modify this query.
 

 

Khurram

Raja_KT 1246 posts Joined 07/09
27 Nov 2013

Hi,
It seems you have two tables to be joined table1 with (amt and calendar date..) and another table is sales table keeping track of order date status till closure.
I suggest you put some fields and values so that we can  have a better idea of the problems.
Getting business or end user requirement is important.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

qbeg 3 posts Joined 11/12
27 Nov 2013

Could you send the example in the form of tables?

Because if was in order to one table and one date after which it is necessary to be to sum it up the code could look this way:
 

SELECT 
cast((order_date(FORMAT 'Y4MM')) as char(6)) as period
,sum(amount) as sum_of_amount

FROM Database.tablename

 

Kawish_Siddiqui 37 posts Joined 03/07
02 Dec 2013

Peiter,
Are you looking for something similar to following?

Select cast((order_date(FORMAT 'MM-YYYY')) as char(7)) as Cal_Month, Sum(OPP.Expected_Amount)
From Opportunity OPP
Inner Join Sales SL
On OPP.Opportunity_ID = SL.Opportunity_ID
Group by 1

 

Kawish Siddiqui -

s@ir@m 35 posts Joined 05/13
23 Jan 2014

hi all,
 
i have a transaction table columns like 
T_ID,T_DATE,T_AMOUNT .
 
Q : i need amount from date wise,and t_amount wise like bellow . 
SELECT T_ID,JAN_T_AMOUNT,FEB_T_AMOUNT,MAR_T_AMOUNT 
is it possible ?.
send me ASAP

You must sign in to leave a comment.