0

My record comes as below after i done the join..

Application     Product     Collateral_Type   Loan_Amount Collateral_Value 
Application#1   ProductA    CollateralTypeX   $1000         $800  
Application#2   ProductA    CollateralTypeX   $2000         $1200 
Application#2   ProductA    CollateralTypeY   $2000         $300

Please advice if there is any way for me to do the summation just by the product and avoid the duplication of amount due to the Collateral_type... It should be like this..

Application#1   $1000  
Application#2   $2000

in reply to Adish...

Table 1

Application No

Product

Loan_Amount

Table 2

Application No

Collateral_Type

Collateral_Value

I came across with the determinant in cognos framework .. but i cant work it out correctly..

appreciate for advice again how should i set up the determinant correctly so that the loan amount value will not be doubled by multiple number of collateral..

Chooi Ling Si Toh
  • 55
  • 1
  • 8

2 Answers2

0

You can use subquery to get DISTINCT data and then do aggregation:

SELECT sub.Application, SUM(Loan_Amount) AS total
FROM (SELECT DISTINCT Application, Product, Loan_Amount
      FROM your_table) AS sub
GROUP BY sub.Application;

LiveDemo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • thank u lad2025 for the demo. i will try to check how can i incorporate this in to cognos fm. but do u have any idea how can i apply the fm determinant on handling this ? this is because the first table i showed is the resulted view after i done my join... i need to prepare the list of column as per shown as the package which is to be published for report authoring purposes.. however.. the loan amount tends to doubled after the joined.. especially when come to crosstab.. – Chooi Ling Si Toh Nov 15 '15 at 03:05
0

To me, it looks like you want max() (or min() or avg()), not sum():

select application, max(Loan_Amount)
from (<whatever>) t
group by application;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786