Please help me solve this query...

  • 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

  • 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

  • To add to what Drew said, please see the links in my signature line.

  • 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

  • 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.

  • Wow Good one.

    Probably I am addicted too much to cursors.

    My Brains are revolving around those nasty cursors.

  • Jeff has an excellent article[/url] on running totals, you might want to have a look...

    --Ramesh


  • 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...

    @steve-2

    Thanks a lot for the solution, that works perfectly.

    @naresh

    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