Improve company productivity with a Business Account.Sign Up

x
?
Solved

Complicated SUM()

Posted on 2006-06-22
9
Medium Priority
?
407 Views
Last Modified: 2008-03-03
The table I'm working in has 4 columns that I am worried about: Program, direct, df_date and class.

What I need to do is a sum of direct with a group_by program and df_date where class = 'AC'.  Simple enough.  However, the values I need to pull for the sum need to be a sum of all items on that date and all previous dates.

So sum(direct at Date1) = sum(direct at Date 1)
   sum(direct at Date2) = sum(direct at Date 2) + sum(direct at Date 1)
etc.

At any one run of the query it would only be getting data for one Program.  So there will probably be another part of the Where - Program = "PID".  Right now I'm just trying to see if this is a concept I should try and pursue (sum on the fly) or if I should try and find a different way.

Also, this is going to be part of a coldfusion page, which is  why I would like to do it with one query.

Any tips would be very helpful.

John
0
Comment
Question by:Pawlikj
6 Comments
 
LVL 10

Expert Comment

by:pai_prasad
ID: 16963823
post the following.

1. table struct with col name and data type
2. input data ...
3. sample o/p data required...

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16963864
sounds like

select A.program,A.dfdate,sUm(b.direct) as total
 from yourtable as A
 Inner Join Yourtable as B
 on a.program = b.program
 and a.dfdate >= b.dfdate
 and a.class=b.class
 where a.class='AC'
 group by a.program,a.dfdate
 order by 1,2 desc
0
 

Author Comment

by:Pawlikj
ID: 16964218
The data I get back when I run

SELECT program, df_date, SUM(direct) AS acwp FROM opp.tphase WHERE class = 'AC' AND program = 'E01043' GROUP BY program, df_date

is:
Program     DF_Date         ACWP  
E01043       10/30/2005     1204.55
E01043       11/27/2005     711.08
E01043       12/25/2005      26.71

When I run your query, it should spit out:
Program     DF_Date         ACWP  
E01043       10/30/2005     1204.55
E01043       11/27/2005     1915.63   <-----1204.55 + 711.08
E01043       12/25/2005     1942.34   <-----1204.55 + 711.08 + 26.71

What I am getting though is:
Program     DF_Date         ACWP  
E01043       10/30/2005     3613.65
E01043       11/27/2005     5746.89
E01043       12/25/2005     5827.02

It is giving me 3 times the value it should be.

0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
LVL 32

Accepted Solution

by:
Brendt Hess earned 500 total points
ID: 16964641
This should do what you want.

SELECT tp.program,
    tp.df_date,
    (SELECT SUM(x.direct) FROM opp.tphase x
     WHERE  x.program = tp.program
        AND x.df_date <= tp.df_date
        AND x.class = tp.class
    ) AS ACWP
FROM opp.tphase tp
WHERE tp.class='AC'
GROUP BY tp.program,
    tp.df_date
ORDER BY tp.program,
    tp.df_date
0
 
LVL 5

Expert Comment

by:nicolasdiogo
ID: 16964896
hi,

just a thought


select

sum( direct ) +       -- today s value
      -- yesterday s value
      (
      select sum( direct ) from @tb ytr -- yesterday
      where
      dateAdd( day , -1 , now.df_date )
      =
      ytr.df_date
      )

from
  @tb            now      -- today

where class = 'AC'

group by Program, df_date


nicolas

www.brainpowered.net
0
 
LVL 5

Assisted Solution

by:nicolasdiogo
nicolasdiogo earned 500 total points
ID: 16964904
select

sum( direct ) +      -- today s value
     -- yesterday s value
     (
     select sum( direct ) from @tb ytr -- yesterday
     where
     dateAdd( day , -1 , now.df_date )
     =
     ytr.df_date

     group by df_date
     )

from
  @tb          now     -- today

where class = 'AC'

group by Program, df_date


sorry, i added the group by in the inner query
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question