Forum Stats

  • 3,740,428 Users
  • 2,248,254 Discussions
  • 7,861,249 Comments

Discussions

Create view with calculation of using aggregrate function in Oracle

user12251389
user12251389 Member Posts: 306 Blue Ribbon
edited Aug 14, 2020 3:28AM in SQL & PL/SQL

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 ?

Tagged:
Saubhikuser12251389

Best Answer

  • chris227
    chris227 Member Posts: 3,513 Bronze Crown
    edited Aug 13, 2020 10:48AM Accepted Answer
    --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
    Saubhikuser12251389user12251389

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,700 Red Diamond
    edited Aug 13, 2020 9:31AM

    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. );
    9. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',8.0426,9982313.29,'CommonShares');
    10. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',7.186,8919089.77,'CommonShares');
    11. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',5.0382,6253318.14,'CommonShares');
    12. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',4.8923,6072222.9,'CommonShares');
    13. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',4.273,5303577.93,'CommonShares');
    14. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',3.6568,4538714.24,'CommonShares');
    15. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',2.564,3182350.48,'CommonShares');
    16. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',2.4302,3016369,'CommonShares');
    17. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',2.3514,2918447.76,'CommonShares');
    18. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',2.3046,2860398.55,'CommonShares');
    19. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',0.1833,227450.88,'Cash');
    20. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',0.1812,224980.4,'Cash');
    21. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('IE0031069382',0.0806,100089.39,'Cash');
    22. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',5.8697,7264193.39,'CommonShares');
    23. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',5.6834,7033686.92,'CommonShares');
    24. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',5.4946,6800033.58,'CommonShares');
    25. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',5.4077,6692525.6,'CommonShares');
    26. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',4.9289,6099902.95,'CommonShares');
    27. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',4.5888,5678999.49,'CommonShares');
    28. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',4.567,5652088.55,'CommonShares');
    29. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',4.3649,5401906.25,'CommonShares');
    30. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',0.1517,187726.9,'Cash');
    31. INSERTINTOIS_ID(IDENTIFIER,ALLOCATIONPERCENTAGE,MARKETVALUEHELD,ALLOCATIONASSETTYPE)VALUES('LU0138075311',0.0444,54982.42,'Cash');
    32. 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.

  • chris227
    chris227 Member Posts: 3,513 Bronze Crown
    edited Aug 13, 2020 10:48AM Accepted Answer
    --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
    Saubhikuser12251389user12251389
  • user12251389
    user12251389 Member Posts: 306 Blue Ribbon
    edited Aug 14, 2020 2:57AM

    @chris227 thanks its working as expected

Sign In or Register to comment.