Solved

Complicated SUM()

Posted on 2006-06-22
9
388 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
9 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 32

Accepted Solution

by:
bhess1 earned 125 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 125 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now