# I need to get the percentage of sum of grouped rows using SQL DB2

Solution for I need to get the percentage of sum of grouped rows using SQL DB2
is Given Below:

Below is the table :

AccountNo Symbol Amount
A1 IBM 10
A1 CSCO 20
A1 GOOG 30
A2 IBM 40
A2 FB 10

I need to get the Percentage of IBM on Account A1. i.e. 10 * 100 / 60 = 16.6%

I need to get the Percentage of CSCO on Account A1.i.e. 20 * 100 / 60 = 33.33%

I need to get the Percentage of GOOG on Account A1.i.e. 30 * 100 / 60 = 50 %

I need to get the Percentage of IBM on Account A2.i.e. 40 * 100 / 50 = 80%

I need to get the Percentage of FB on Account A2.i.e. 10 * 100 / 50 = 20%

I tried below query but does not execute:

``````select AccountNo, SYMBOL, Value, Float(cast((value(Amount,0)) as DECIMAL(18,2))) / FLOAT(cast((value(t2.total,0)) as DECIMAL (18,2))) * 100
from mytable
(select sum(Amount) as total from mytable group by AccountNo) as T2
where Amount > 0
group by AccountNo, SYMBOL, Value, T2.total;
``````

You can use window functions:

``````select account, symbol,
amount * 100.0 / sum(amount) over (partition by account) as percentage
from t;
``````

If `amount` is actually a calculation in an aggregation, you can incorporate this directly in the aggregation query. For example:

``````select account, symbol,
sum(amount) * 100.0 / sum(sum(amount)) over (partition by account) as percentage
from t
group by account, symbol;
``````

There is a convenient function RATIO_TO_REPORT in Db2 for LUW:

``````WITH T (AccountNo, Symbol, Amount) AS
(
VALUES
('A1', 'IBM',  10)
, ('A1', 'CSCO', 20)
, ('A1', 'GOOG', 30)
, ('A2', 'IBM',  40)
, ('A2', 'FB',   10)
)
SELECT
T.*
, DEC (100 * RATIO_TO_REPORT (Amount) OVER (PARTITION BY AccountNo), 5, 2) R
FROM T;
``````