How do I select unique values in a single column with multiple column selected?-CodePudding

the query is actually fine and running but it doesn't select distinct the column A.CONTRACT_NUMBER.

This is the code:

SELECT DISTINCT A.CONTRACT_NUMBER
, A.DTIME_SIGNATURE
, A.ID_CUID
, A.CLIENT_SEGMENT
, A.CLIENT_GENDER
, A.CLIENT_AGE
, A.CNT_SIGNED
, A.AMT_SIGNED
, A.INSTALMENTS
, A.PRODUCT
, B.AMT_INTEREST_INCOME
  FROM DM_SALES.V_SALES_DM_DATA A
  LEFT JOIN DM_FIN.FIN_LTV_DATAMART B ON A.CONTRACT_NUMBER = B.CONTRACT_NUMBER
  WHERE 1=1
  AND A.CONTRACT_STATE <> 'Cancelled' 
  AND a.cnt_signed=1 
  AND A.LOAN_TYPE = 'Consumer Loan'
  AND (TRUNC(A.DTIME_SIGNATURE) BETWEEN DATE'2022-08-01' AND DATE '2022-08-31')
  GROUP BY A.CONTRACT_NUMBER
  ORDER BY A.DTIME_SIGNATURE
;

It runs normally but after checking the data, there are still repeated values in the A.CONTRACT_NUMBER column. A.CONTRACT_NUMBER is like the primary key column and I'd like to select unique values to that column

Thanks! :D

CodePudding user response：

Per contract you want the average interest income from your datamart table, so aggregate your datamart data by contract and join this result to your sales data table.

SELECT
  sd.*,
  dm.avg_interest_income
FROM dm_sales.v_sales_dm_data sd
LEFT JOIN 
(
  SELECT contract_number, AVG(amt_interest_income) AS avg_interest_income
  FROM dm_fin.fin_ltv_datamart
  GROUP BY contract_number
) dm ON dm.contract_number = sd.contract_number
WHERE sd.contract_state <> 'Cancelled' 
  AND sd.cnt_signed = 1 
  AND sd.loan_type = 'Consumer Loan'
  AND sd.dtime_signature >= DATE '2022-08-01'
  AND sd.dtime_signature < DATE '2022-09-01'
ORDER BY sd.dtime_signature;