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
.