[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

T-SQL:  Cumulative SUM

Posted on 2014-07-17
10
Medium Priority
?
622 Views
Last Modified: 2014-07-19
Hello:

Attached is my T-SQL query.  Also, attached are its results.

My focus, primarily, is on the YRACCUMDEP field.  I need for this field to be a "cumulative sum" of the DEPEXP4YR field.  For each YR field, YRACCUMDEP needs to sum its amount for DEPEXP4YR plus the amount of DEPEXP4YR from previous YR's.

For example, where YR = "2017", YRACCUMDEP needs to display the sum of 142.86, 130.96, and 166.66.

I don't know that I want this constructed as a CTE.  I say that because, once I get my YRACCUMDEP field to where it suits my needs, I'm going to want the YRNBV field to subtract the COSTBASIS field and the YRACCUMDEP field for each YR.  

Thanks!  Please let me know, if there are any questions.

Much appreciated!

TBSupport
Need-Cumulative-Depreciation.docx
QUERY.docx
0
Comment
Question by:TBSupport
  • 5
  • 5
10 Comments
 
LVL 1

Author Comment

by:TBSupport
ID: 40203114
Anyone?  Anyone?  :)

TBSupport
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40203467
outputTo accumulate a calculated column into another column can be achieved by "nesting" (there is no need for a CTE)

However please also note you should solve the issue of DISTINCT before you start summing anything. Not only is it wasteful to be calculating rows that are eventually dispensed with via "select distinct" it is also potentially wrong (can produce the wrong figures).

If you run your existing query without DISTINCT what happens?
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40204291
Hi PortletPaul!

When I remove DISTINCT, I get lots of duplicated rows.  For example, my query is returning 56 rows.  Upon removing DISTINCT, I receive 334 rows--many of them duplicates.

TBSupport
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40204310
All of them are "unwanted repetition" (duplicated records are a different thing altogether, here we are dealing with a "resultset" not actual records).

This is produced by one or more of the joins you are using that multiply the rows.

You have to find which of those joins is doing the multiplication and use distinct or group by on that data BEFORE you join. Otherwise your arithmetic could be wrong.

"Select distinct" on the outer query is not the right way out of this issue.


http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_12282-Select-Distinct-is-returning-duplicates.html
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40204348
OK.  I'll give those a try.

In the meantime, can you also send me a link please for the "nesting" capability that you mentioned earlier?  That way, I can "kill two birds with one stone".

Thanks!

TBSupport
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40204469
"nesting" is simple

select
 *
, sum(summed_amount) over(....)
from (
           select
             *
            , sum(amount) as summed_amount
            , row_number() over(partition by x,y,z order by a,b DESC) as rn
           from x
           where y = z
           ) as nested
where rn = 1


For this situation, you want to "nest" the query in your question inside another, that will allow reference to the outcome of sum() over() or row_number() etc by their aliases

I have seen you do this before now, perhaps you use a different word to describe it.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40205330
Hi:

Below is what I just tried.  With this, I get the error "Subquery returned more than 1 value. This is not permitted when the subquery follows..."

Any ideas?

TBSupport

SELECT ASSETID, COSTBASIS, DEPEXP4YR, 
(SELECT SUM(DEPEXP4YR)
                       FROM TWO.dbo.FA00902 b
                       WHERE test.YR <= b.FAYEAR) as YRACCUMDEP,
YRNBV FROM
(
SELECT DISTINCT TWO.dbo.FA00100.ASSETID AS ASSETID, 
TWO.dbo.FA00100.SHRTNAME AS SHORTNAME, 
TWO.dbo.FA00200.PLINSERVDATE AS PLACEINSERVICEDATE, TWO.dbo.FA00200.ORIGINALLIFEYEARS AS ORIGINALLIFE, 
TWO.dbo.FA00200.COSTBASIS AS COSTBASIS, TWO.dbo.FA00902.FAYEAR AS YR,
SUM(TWO.dbo.FA00902.AMOUNT) OVER (PARTITION BY TWO.dbo.FA00902.ASSETINDEX, 
TWO.dbo.FA00902.FAYEAR, TWO.dbo.FA00902.BOOKINDX) AS DEPEXP4YR, 
SUM(TWO.dbo.FA00902.AMOUNT) OVER (PARTITION BY TWO.dbo.FA00902.ASSETINDEX, 
TWO.dbo.FA00902.BOOKINDX) AS YRACCUMDEP, 
TWO.dbo.FA00200.COSTBASIS - (SUM(TWO.dbo.FA00902.AMOUNT) OVER (PARTITION BY TWO.dbo.FA00902.ASSETINDEX, 
TWO.dbo.FA00902.BOOKINDX)) as YRNBV,
TWO.dbo.GL00105.ACTNUMST as DEPEXPACCOUNT,
CASE WHEN TWO.dbo.FA00200.DEPRECIATIONMETHOD = '1' 
THEN 'Straight-Line Original Life' WHEN TWO.dbo.FA00200.DEPRECIATIONMETHOD = '2' 
THEN 'Straight-Line Remaining Life' ELSE '' END as METHOD, 
TWO.dbo.FA40200.BOOKID AS BOOK, 'Fabrikam, Inc.' as COMPANY 
FROM TWO.dbo.FA00902 
INNER JOIN TWO.dbo.FA00100 ON TWO.dbo.FA00902.ASSETINDEX = TWO.dbo.FA00100.ASSETINDEX 
INNER JOIN TWO.dbo.FA00200 ON TWO.dbo.FA00902.ASSETINDEX = TWO.dbo.FA00200.ASSETINDEX 
AND TWO.dbo.FA00902.BOOKINDX = TWO.dbo.FA00200.BOOKINDX 
INNER JOIN TWO.dbo.FA40200 ON TWO.dbo.FA00200.BOOKINDX = TWO.dbo.FA40200.BOOKINDX
INNER JOIN TWO.dbo.GL00105 ON TWO.dbo.FA00902.GLINTACCTINDX = TWO.dbo.GL00105.ACTINDX 
WHERE TWO.dbo.FA00902.SOURCDOC LIKE 'FADEP%' AND TWO.dbo.FA00902.TRANSACCTTYPE = '2'
--REMOVE THE FOLLOWING CLAUSE PRIOR TO "GO-LIVE":
and TWO.dbo.FA40200.BOOKID = 'INTERNAL' --and TWO.dbo.FA00902.FAYEAR = '2017'
)
as test
GROUP BY ASSETID, COSTBASIS, DEPEXP4YR, YR, YRACCUMDEP, YRNBV

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40205822
PLEASE use code blocks. I edited the above to do this.
------------------------------------------------------------------------

When attempting to eradicate unwanted repetition you need to look deeper. Adding another layer on top isn't going to solve the initial problem.

In the following there are 3 source records each with a value of 100. So the grand total is 300. Only the last of these 4 queries gets the correct figures, which it does by solving the repetition before aggregating):
**MS SQL Server 2008 Schema Setup**:

    
    
    CREATE TABLE SourceData	
    	([ID] int, [AssocNum] int, [Value] int)
    ;
    	
    INSERT INTO SourceData	
    	([ID], [AssocNum], [Value])
    VALUES
    	(1, 2015, 100),
    	(2, 2016, 100),
    	(5, 2017, 100)
    ;
    
    
    
    CREATE TABLE AssoociatedData	
    	([ID] int, [Num] int)
    ;
    	
    INSERT INTO AssoociatedData	
    	([ID], [Num])
    VALUES
    	(1, 2015),
    	(2, 2015),
    	(3, 2016),
    	(4, 2016),
    	(5, 2017),
    	(6, 2017),
    	(7, 2017)
    ;

**Query 1**:

    SELECT
          AssocNum
        , SUM(Value) AS sum_value
    FROM SourceData AS d
          INNER JOIN AssoociatedData AS a
                      ON d.AssocNum = a.Num
    GROUP BY
          AssocNum
    

**[Results][2]**:
    
    | ASSOCNUM | SUM_VALUE |
    |----------|-----------|
    |     2015 |       200 |
    |     2016 |       200 |
    |     2017 |       300 |


**Query 2**:

    SELECT
          AssocNum
        , SUM(Value) over() AS sum_value
    FROM SourceData AS d
          INNER JOIN AssoociatedData AS a
                      ON d.AssocNum = a.Num
    

**[Results][3]**:
    
    | ASSOCNUM | SUM_VALUE |
    |----------|-----------|
    |     2015 |       700 |
    |     2015 |       700 |
    |     2016 |       700 |
    |     2016 |       700 |
    |     2017 |       700 |
    |     2017 |       700 |
    |     2017 |       700 |


**Query 3**:

    SELECT DISTINCT
          AssocNum
        , SUM(Value) over() AS sum_value
    FROM SourceData AS d
          INNER JOIN AssoociatedData AS a
                      ON d.AssocNum = a.Num
    

**[Results][4]**:
    
    | ASSOCNUM | SUM_VALUE |
    |----------|-----------|
    |     2015 |       700 |
    |     2016 |       700 |
    |     2017 |       700 |


**Query 4**:

    SELECT
          AssocNum
        , SUM(Value) AS sum_value
    FROM SourceData AS d
          INNER JOIN (SELECT DISTINCT Num FROM AssoociatedData) AS a
                      ON d.AssocNum = a.Num
    GROUP BY
          AssocNum
    

**[Results][5]**:
    
    | ASSOCNUM | SUM_VALUE |
    |----------|-----------|
    |     2015 |       100 |
    |     2016 |       100 |
    |     2017 |       100 |



  [1]: http://sqlfiddle.com/#!3/19e42/5

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40205833
It's solved?
0
 
LVL 1

Author Comment

by:TBSupport
ID: 40206403
To be honest, no.  I'm just going to move on and ask one of my T-SQL colleagues to help me out with this or even ask him to take over the project.  This stuff is beyond my comprehension.

Goodbye.

TBSupport
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

607 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