Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Complicated SUM()

Posted on 2006-06-22
9
Medium Priority
?
401 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how the fundamental information of how to create a table.
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…

636 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