Database

The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

I have a data set where i need to distribute the amount of col1 into col2 and i need to calculate the columns "new amount col2" and "New amount col1"

col1 | col2 | amount col1 | division | typee | new amount col2 | New amount col1 |

KS13 | KS13 | 1,907.69 | 0.60000 | percent | 1144.614 | 1144.614 |

KS13 | CS2 | 1,907.69 | 0.40000 | percent | 763.076 | 1144.614 |

KS13 | CS3 | 1,907.69 | 100 | fixed | 100 | 1044.614 |

KS13 | CS4 | 1,907.69 | 1,000 | fixed | 1000 | 44.614 |

CC1 | CCS | 1,644.32 | 1,200 | fixed | 1200 | 444,32 |

...

SyntaxEditor Code Snippet

SELECT col1,col2,amountcol1,division, SUM( CASE WHEN typee='percent' THEN amountcol1*division WHEN typee ='fixed' THEN division END) AS newamountcol2 FROM table1

now i cant figure out how to calculate "Newamountcol1"

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

I don't understand how you calculate *Newamountcol1*.

What's the relation between *fixed* & *percent* amounts regaring this cumulative sum?

And how is the order of rows defined?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

Highlighted
##
##### Re: calculate cumulative subtraction

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

You need an order to calculate a cumulative sum and if you want to be able to repeatedly get the same result it must be a unique sort order.