Database

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

4 weeks ago

4 weeks ago

Hi!

I'm new with Teradata and I need help...

I have this data:

MONTH | BRAND | COLOR | AMOUNT |

201601 | BRAND1 | BLUE | 13719,03 |

201601 | BRAND1 | PINK | 36,89 |

201601 | BRAND1 | BLACK | 127864,51 |

201601 | BRAND1 | GRAY | 23992,22 |

201601 | BRAND1 | PURPLE | 1199 |

201601 | BRAND1 | YELLOW | 52583,27 |

And I need this:

MONTH | BRAND | COLOR | AMOUNT | TOTAL_AMOUNT | PERCENTAGE |

201601 | BRAND1 | BLUE | 13719,03 | 219394,92 | 6.25 |

201601 | BRAND1 | PINK | 36,89 | 219394,92 | 0.02 |

201601 | BRAND1 | BLACK | 127864,51 | 219394,92 | 58.28 |

201601 | BRAND1 | GRAY | 23992,22 | 219394,92 | 10.94 |

201601 | BRAND1 | PURPLE | 1199 | 219394,92 | 0.55 |

201601 | BRAND1 | YELLOW | 52583,27 | 219394,92 | 23.96 |

I want to use OLAP functions... I was trying this:

SELECT A.*, SUM(A.AMOUNT) OVER (PARTITION BY A.MONTH, A.BRAND ROWS UNBOUNDED PRECEDING) AS TOTAL_AMOUNT, (AMOUNT/(SUM(A.AMOUNT) OVER (PARTITION BY A.MONTH, A.BRAND ROWS UNBOUNDED PRECEDING)))*100 AS PERCENTAGE

FROM (SELECT MONTH, BRAND, COLOR, SUM(AMOUNT) AS AMOUNT FROM MY.TABLE GROUP BY MONTH, BRAND, COLOR) A;

(I changed columns and table names, sorry for any reserved words)

I'm getting this:

MONTH | BRAND | COLOR | AMOUNT | TOTAL_AMOUNT | PERCENTAGE |

201601 | AOC | YELLOW | 52583.27 | 52583.27 | 100.00 |

201601 | AOC | BLACK | 127864.51 | 180447.78 | 71.00 |

201601 | AOC | PINK | 36.89 | 180484.67 | 0.00 |

201601 | AOC | BLUE | 13719.03 | 194203.70 | 7.00 |

201601 | AOC | GRAY | 23992.22 | 218195.92 | 11.00 |

201601 | AOC | PURPLE | 1199.00 | 219394.92 | 1.00 |

So I have two problems with my query:

1- Cumulative column when I need a sum column

2- Percentage as integer with .00 when I need a decimal 6,2

Thank you!

Solved! Go to Solution.

Accepted Solutions

Highlighted

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

4 weeks ago

4 weeks ago

It looks like all you need to do is take out the rows-unbounded-preceding phrases (which imply cumulation) and cast the percentage.

SELECT A.*

,SUM(A.AMOUNT) OVER (PARTITION BY A.MONTH, A.BRAND) AS TOTAL_AMOUNT

,(AMOUNT/(SUM(A.AMOUNT) OVER (PARTITION BY A.MONTH, A.BRAND)))*100 **(DEC(6,2))** AS PERCENTAGE

FROM (SELECT MONTH, BRAND, COLOR, SUM(AMOUNT) AS AMOUNT

FROM MY.TABLE

GROUP BY MONTH, BRAND, COLOR) A;

1 ACCEPTED SOLUTION

5 REPLIES

Highlighted

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

4 weeks ago

4 weeks ago

SELECT A.*

,SUM(A.AMOUNT) OVER (PARTITION BY A.MONTH, A.BRAND) AS TOTAL_AMOUNT

,(AMOUNT/(SUM(A.AMOUNT) OVER (PARTITION BY A.MONTH, A.BRAND)))*100 **(DEC(6,2))** AS PERCENTAGE

FROM (SELECT MONTH, BRAND, COLOR, SUM(AMOUNT) AS AMOUNT

FROM MY.TABLE

GROUP BY MONTH, BRAND, COLOR) A;

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

4 weeks ago

4 weeks ago

Thank you, Coleman!

Group sum worked very well!

Cast didn't work, still rounding and displaying with .00

I'm using version 15. I'll keep trying ;)

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

4 weeks ago

4 weeks ago

Thanks, Coleman!

It worked casting columns as float ;)

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

4 weeks ago

4 weeks ago

No need for casting to Float, follow the basic rule: *"multiply first, then divide":*

100*AMOUNT/SUM(A.AMOUNT) OVER (PARTITION BY A.MONTH, A.BRAND)

Teradata calculates the resulting number of fractional digits based on max of higher precision of both operands and rounds after each step (i.e. doesn't carry higher internal precision), e.g.

2/3.00*100 = (2/3.00) * 100 = 0.67 * 100 = 67.00

100*2/3.00 = (100*2) / 3.00 = 200 / 3.00 = 66.67

- 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

Thank you, dnoeth! I'll try it! ;)