Sum by group

Database
Enthusiast

Sum by group

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

I have this data:

 

MONTHBRANDCOLORAMOUNT
201601BRAND1BLUE13719,03
201601BRAND1PINK36,89
201601BRAND1BLACK127864,51
201601BRAND1GRAY23992,22
201601BRAND1PURPLE1199
201601BRAND1YELLOW52583,27

 

 

And I need this:

 

MONTHBRANDCOLORAMOUNTTOTAL_AMOUNTPERCENTAGE
201601BRAND1BLUE13719,03219394,926.25
201601BRAND1PINK36,89219394,920.02
201601BRAND1BLACK127864,51219394,9258.28
201601BRAND1GRAY23992,22219394,9210.94
201601BRAND1PURPLE1199219394,920.55
201601BRAND1YELLOW52583,27219394,9223.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:

 

MONTHBRANDCOLORAMOUNTTOTAL_AMOUNTPERCENTAGE
201601AOCYELLOW52583.2752583.27100.00
201601AOCBLACK127864.51180447.7871.00
201601AOCPINK36.89180484.670.00
201601AOCBLUE13719.03194203.707.00
201601AOCGRAY23992.22218195.9211.00
201601AOCPURPLE1199.00219394.921.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!

 

 

 


Accepted Solutions
Highlighted
Teradata Employee

Re: Sum by group

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
Teradata Employee

Re: Sum by group

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;

Enthusiast

Re: Sum by group

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 ;)

Enthusiast

Re: Sum by group

Thanks, Coleman!

 

It worked casting columns as float ;)

Junior Contributor

Re: Sum by group

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

Enthusiast

Re: Sum by group

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