Create view with calculation of using aggregrate function in Oracle

user12251389

I have below table IS_ID:

   

I want to create View based on this table with having some calculation.  For each Identifier there will be two rows, one for ALLOCATIONASSETTYPE = 'Cash' and second for ALLOCATIONASSETTYPE = 'Other'

In first Row where ALLOCATIONASSETTYPE = 'Cash':

For example for each same IDENTIFIER  i wanted to sum the ALLOCATIONPERCENTAGE where ALLOCATIONASSETTYPE = 'Cash'.

For each same Identifier i wanted to sum MARKETVALUEHELD where ALLOCATIONASSETTYPE = 'Cash'.

In second row where ALLOCATIONASSETTYPE = 'Other':

For example i want to do below calculation:

100 - sum of all ALLOCATIONPERCENTAGE where ALLOCATIONASSETTYPE ! = 'Cash' - sum the ALLOCATIONPERCENTAGE where ALLOCATIONASSETTYPE = 'Cash'

At the end it will look like below for example :

(IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 0.4451, 552520.67, 'Cash');(IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 56.8158, null, 'Other');

Is it possible to create View in such case ? Is it possible to keep the existing IDENTIFIER data as well then for each Identifier we can add two more rows for allocationassettype = 'Cash' and allocationassettype = 'Other'? So i can see existing data as well for each Identifier and also see this two new rows ?

chris227

--create view vw_IS_ID--asselect  'agg' source, IDENTIFIER, case when ALLOCATIONASSETTYPE = 'Cash' then ALLOCATIONASSETTYPE else 'others' end ALLOCATIONASSETTYPE, case when case when ALLOCATIONASSETTYPE = 'Cash' then ALLOCATIONASSETTYPE else 'others' end <> 'Cash' then  100 - sum(sum(ALLOCATIONPERCENTAGE)) over (partition by IDENTIFIER)  else sum(ALLOCATIONPERCENTAGE)  end ALLOCATIONPERCENTAGE, case when case when ALLOCATIONASSETTYPE = 'Cash' then ALLOCATIONASSETTYPE else 'others' end <> 'Cash'       then null       else sum(MARKETVALUEHELD)  end MARKETVALUEHELDfrom IS_IDgroup by  IDENTIFIER, case when ALLOCATIONASSETTYPE = 'Cash' then ALLOCATIONASSETTYPE else 'others' endunion allselect'origin',IDENTIFIER,ALLOCATIONASSETTYPE, ALLOCATIONPERCENTAGE, MARKETVALUEHELDfrom is_idorder by IDENTIFIER, source desc

Frank Kulash

Hi,

user12251389 wrote:I have below table IS_ID:

1. CREATETABLE
2. IS_ID
3. (
4. IDENTIFIERVARCHAR2(50),
5. ALLOCATIONPERCENTAGENUMBER(19,8),
6. MARKETVALUEHELDNUMBER(19,8),
7. ALLOCATIONASSETTYPEVARCHAR2(100)
8. );
10. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',8.0426,9982313.29,'CommonShares');
11. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',7.186,8919089.77,'CommonShares');
12. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',5.0382,6253318.14,'CommonShares');
13. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',4.8923,6072222.9,'CommonShares');
14. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',4.273,5303577.93,'CommonShares');
15. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',3.6568,4538714.24,'CommonShares');
16. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',2.564,3182350.48,'CommonShares');
17. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',2.4302,3016369,'CommonShares');
18. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',2.3514,2918447.76,'CommonShares');
19. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',2.3046,2860398.55,'CommonShares');
20. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',0.1833,227450.88,'Cash');
21. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',0.1812,224980.4,'Cash');
22. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',0.0806,100089.39,'Cash');
23. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',5.8697,7264193.39,'CommonShares');
24. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',5.6834,7033686.92,'CommonShares');
25. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',5.4946,6800033.58,'CommonShares');
26. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',5.4077,6692525.6,'CommonShares');
27. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',4.9289,6099902.95,'CommonShares');
28. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',4.5888,5678999.49,'CommonShares');
29. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',4.567,5652088.55,'CommonShares');
30. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',4.3649,5401906.25,'CommonShares');
31. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',0.1517,187726.9,'Cash');
32. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',0.0444,54982.42,'Cash');
33. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',0,-0.68,'Cash');

CREATE TABLE  IS_ID  (  IDENTIFIER VARCHAR2(50),  ALLOCATIONPERCENTAGE NUMBER(19,8),  MARKETVALUEHELD NUMBER(19,8),  ALLOCATIONASSETTYPE VARCHAR2(100)  );   INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 8.0426, 9982313.29, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 7.186, 8919089.77, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 5.0382, 6253318.14, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 4.8923, 6072222.9, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 4.273, 5303577.93, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 3.6568, 4538714.24, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 2.564, 3182350.48, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 2.4302, 3016369, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 2.3514, 2918447.76, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 2.3046, 2860398.55, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 0.1833, 227450.88, 'Cash'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 0.1812, 224980.4, 'Cash'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 0.0806, 100089.39, 'Cash'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 5.8697, 7264193.39, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 5.6834, 7033686.92, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 5.4946, 6800033.58, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 5.4077, 6692525.6, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 4.9289, 6099902.95, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 4.5888, 5678999.49, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 4.567, 5652088.55, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 4.3649, 5401906.25, 'Common Shares'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 0.1517, 187726.9, 'Cash'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 0.0444, 54982.42, 'Cash'); INSERT INTO IS_ID (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('LU0138075311', 0, -0.68, 'Cash');

I want to create View based on this table with having some calculation. For each Identifier there will be two rows, one for ALLOCATIONASSETTYPE = 'Cash' and second for ALLOCATIONASSETTYPE = 'Other'In first Row where ALLOCATIONASSETTYPE = 'Cash':For example for each same IDENTIFIER i wanted to sum the ALLOCATIONPERCENTAGE where ALLOCATIONASSETTYPE = 'Cash'.For each same Identifier i wanted to sum MARKETVALUEHELD where ALLOCATIONASSETTYPE = 'Cash'.In second row where ALLOCATIONASSETTYPE = 'Other':For example i want to do below calculation:100 - sum of all ALLOCATIONPERCENTAGE where ALLOCATIONASSETTYPE ! = 'Cash' - sum the ALLOCATIONPERCENTAGE where ALLOCATIONASSETTYPE = 'Cash'At the end it will look like below for example :

1. (IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',0.4451,552520.67,'Cash');
2. (IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',56.8158,null,'Other');

(IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 0.4451, 552520.67, 'Cash'); (IDENTIFIER, ALLOCATIONPERCENTAGE, MARKETVALUEHELD, ALLOCATIONASSETTYPE) VALUES ('IE0031069382', 56.8158, null, 'Other');

Is it possible to create View in such case ?

Thanks for posting the CREATE TABLE and INSERT statements.  Don't forget to post your Oracle version,

Will the view only have two rows?

If so, how do you derive the first column?

If not, post the complete contents of the view, given the sample data you posted.

EdStevens

Also asked at https://stackoverflow.com/questions/63395895/create-view-with-calculation-of-using-aggregrate-function-in-oracle

chris227

--create view vw_IS_ID--asselect  'agg' source, IDENTIFIER, case when ALLOCATIONASSETTYPE = 'Cash' then ALLOCATIONASSETTYPE else 'others' end ALLOCATIONASSETTYPE, case when case when ALLOCATIONASSETTYPE = 'Cash' then ALLOCATIONASSETTYPE else 'others' end <> 'Cash' then  100 - sum(sum(ALLOCATIONPERCENTAGE)) over (partition by IDENTIFIER)  else sum(ALLOCATIONPERCENTAGE)  end ALLOCATIONPERCENTAGE, case when case when ALLOCATIONASSETTYPE = 'Cash' then ALLOCATIONASSETTYPE else 'others' end <> 'Cash'       then null       else sum(MARKETVALUEHELD)  end MARKETVALUEHELDfrom IS_IDgroup by  IDENTIFIER, case when ALLOCATIONASSETTYPE = 'Cash' then ALLOCATIONASSETTYPE else 'others' endunion allselect'origin',IDENTIFIER,ALLOCATIONASSETTYPE, ALLOCATIONPERCENTAGE, MARKETVALUEHELDfrom is_idorder by IDENTIFIER, source desc

user12251389

@chris227 thanks its working as expected