Solved

Complicated SUM()

Posted on 2006-06-22
9
393 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running total between 2 sql tables in Sql 6 50
ms sql and asp dates 5 42
Format Output of Select Statement 2 38
T-SQL: need to reset a declared variable 4 31
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

732 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