Home > Software engineering >  How to SUM a column in an oracle apex collection
How to SUM a column in an oracle apex collection

Time:02-28

So I am trying to output the total of a column from a collection.

This is the first query I tried

select sum(C007),sum(C007) A FROM APEX_COLLECTIONS WHERE COLLECTION_NAME='PURCHASE'

This is the second query I tried

select sum(C005*C007),sum(C005*C007) A FROM APEX_COLLECTIONS WHERE COLLECTION_NAME='PURCHASE'

Both produce the same result which list out all the values in the column insted of suming them

Expected Results:

10

Actual Results:

2
2
2
2
2

Please help

CodePudding user response:

Looks like you did something wrong.

I created sample page; it contains a button (which will just submit the page) and an item which will display total (sum of collection's values). Item gets populated by a process which contains everything (for simplicity):

if not apex_collection.collection_exists('PURCHASE') then
   apex_collection.create_collection('PURCHASE');
end if;

apex_collection.add_member(
  p_collection_name => 'PURCHASE',
  p_c001            => 'Little',
  p_c007            => 100);              --> 100 ...
apex_collection.add_member(
  p_collection_name => 'PURCHASE',
  p_c001            => 'Foot',
  p_c007            => 200);              --> ...   200 = 300

select sum(c007)
  into :P7_TOTAL
  from apex_collections
  where collection_name = 'PURCHASE';

When ran (and after button was pressed), item's value is - as expected - 300.

enter image description here

  • Related