How to handle this extraction

Question

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..

Answer 1

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

Answer 2

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;