January 10, 2009 at 12:50 am
Hi there,
I have a 'sales' column with concerned to 'date' col
I want to write a query so that it represents three columns
Date Sales Sum(sales)
Sum (sales) is the summition of all the sales values till that date (just like performing sigma operation)
Ex:
Sales Sum(sales)
10 10
20 30
30 60
40 100
50 150
For implementing this query I wanted to give a try using query like
Select sales, sum(sales) from SalesDealer group by sales;
It's failed to provide me correct values, obviously..as this would perform the operation grouping each sales data item seperate...So, it simply returned the Sales values as usual.
I wanted to give a try using Self-Join concept either. But, I didn't get much advantage of it. If this works, Sub Query concept should work too.
I'm wasting my time on this. Please, anybody have suggestions..???
-Thanks in advance
Vsoma
January 11, 2009 at 5:32 pm
You need to be a little more clear about the tables you are dealing with. i am having a hard time figuring out what it means to group by sales if you want the sum of these sales. if you want to group by date, say, you would do something like this:
create table #Sales (SaleDate datetime, Sale money)
insert into #Sales values ('2009-01-01', 10)
insert into #Sales values ('2009-01-01', 20)
insert into #Sales values ('2009-01-01', 30)
insert into #Sales values ('2009-01-02', 5000)
insert into #Sales values ('2009-01-02', 1000)
select saledate, sum(sale) from #Sales
group by saledate
please post a table structure and i can help you.
-drew
January 11, 2009 at 6:12 pm
To add to what Drew said, please see the links in my signature line.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 11, 2009 at 8:35 pm
HI,
we have two ways to display the totals
1. calculated columns
2. add a new permanant column and create a trigger on insert , update , delete to compute the upto current date total.
1st method use a cursor code is below.
declare @date datetime
declare @sum int
create table #sums(date datetime,sums int)
declare my cursor for
select distinct date from test order by date
open my
Fetch next from my into @date
while @@fetch_status = 0
begin
select @sum = sum(sale) from test where date <=@date
insert into #sums values (@date,@sum)
Fetch next from my into @date
end
close my
deallocate my
select t.date,t.sale,s.sums from test t join #sums s on t.date =s.date
Thanks
naresh
January 12, 2009 at 7:33 pm
Anthony Molinaro wrote a book called SQL Cookbook that serves as my right brain, or is it my left brain, for problems just like this.
His example, a scalar subquery, of summing running totals involved a table of employees and their salaries. It goes like this...
select e.ename, e.sal,
(select sum(d.sal) from emp d
where d.empno <= e.empno) as running_total
from emp e
order by 3
I tried it in SS2005 and it worked...maybe it will work for you too. Good luck.
Steve
Someday when I grow up, I hope to know something about SQL.
January 12, 2009 at 8:54 pm
Wow Good one.
Probably I am addicted too much to cursors.
My Brains are revolving around those nasty cursors.
January 13, 2009 at 12:21 am
January 14, 2009 at 10:33 am
Thank you all for responding...
and thanks a lot for the suggestions provided
I solved using the following query
select s.date, s.salesno, sum(d.salesno)as sum11 from SALES s
INNER JOIN
SALES d ON s.date>=d.date group by s.date, s.salesno;
I just wanted to include the date column in that too...
Thanks a lot for the solution, that works perfectly.
Thanks a lot for the solution, that was nice.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply