I need help with updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..

Can you please help?

SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,

(Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU = c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X'  and a.CLAIM_ICN_NU = '1111155555') as #_of_Payer

From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c

where a.CLAIM_ICN_NU = c.CLAIM_ICN and b.TPL_TS = c.TPL_TS

and a.CLAIM_TYPE_CD = 'X'  and a.CLAIM_ICN = '1111155555'

Current Result

FILING_IND	PYR_CD	PAYER_ID	PAYER_NAME	CLAIM_ICN	#_OF_PAYER
CI	U	30006	MONUMENTAL LIFE INSURANCE CO.	1111155555	3
MB	P	5202	WPS - MAC J5 PART B	1111155555	3
MB	P	5202	WPS - MAC J5 PART B	1111155555	3

Desired Result:

FILING_IND	PYR_CD	PAYER_ID	PAYER_NAME	CLAIM_ICN	#_OF_PAYER
CI	U	30006	MONUMENTAL LIFE INSURANCE CO.	1111155555	1
MB	P	5202	WPS - MAC J5 PART B	1111155555	2
MB	P	5202	WPS - MAC J5 PART B	1111155555	2

• Edited by SuhKha 5 hours 56 minutes ago

Why is it 1 for the first and 2 for the second? Number of unique payers on 1111155555 is two whichever way you look at it. For this, you'd have to put count(distinct payer_id), for how you determine 1 and 2, more explanation would be needed

I have corrected the description above. I basically need count of each payer based on number of times it is repeated. Thats why in desired result I need 1 for 1st payer as it appears only once and 2 for 2nd and 3rd as they appeared twice. Hope I have explained the request clearly this time. 

SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,

 Count(*) Over(Partition by  b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN)  as #_of_Payer

From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c

where a.CLAIM_ICN_NU = c.CLAIM_ICN and b.TPL_TS = c.TPL_TS

and a.CLAIM_TYPE_CD = 'X'  and a.CLAIM_ICN = '1111155555'

• Proposed as answer by Naomi NModerator 2 hours 56 minutes ago