Solved

Complicated SUM()

Posted on 2006-06-22
9
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

690 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