CODEWITHSUNDEEP
sqloracle-database
Chuck0185
Chuck0185

Reputation: 531

Count the amount of times that one of the fields in my Oracle SQL Query shows up in the query

I have created the query below. I would like to find out how many times the SSN value from that row shows up in the entire query. I have tried a few different modifications of the below SQL but I haven't been able to come up with the proper syntax. 

SELECT COUNT(SSN), 
    ,CONTROL_ID
    ,SSN
    ,EFF_DATE
    ,FIRST_NAME
    ,LAST_NAME
    ,MIDDLE_INIT
    ,BIRTHDATE
    ,SEX
    ,MARITAL_STATUS
    ,ORIG_DOH
    ,LOCKREC
    ,CONTROLLED_GROUP_STATUS
    ,ALT_SSN
    ,IS_EMP_DEP_IND
    ,DEP_OF_SSN
    ,IS_EMP_BEN_IND
    ,BEN_OF_SSN
    ,SALUTATION
    ,MARITAL_STATUS_CHANGE_DATE
    ,DEATH_DATE
    ,EXECUTIVE_IND
    ,OFFICER_IND
    ,WORKMANS_COMP_EFF_DATE
    ,WORKMANS_COMP_AMOUNT
    ,WORKMANS_COMP_END_DATE
    ,EMAIL_ADDRESS
    ,INTERNET_ADDRESS
    ,SS_DISAB_APP_DATE
    ,SS_DISAB_AMOUNT
    ,SS_DISAB_END_DATE
    ,MAIDEN_NAME
    ,DRO_IND
    ,LAST_TERM_DATE
    ,LATEST_HIRE_DATE
    ,LOA_DATE
    ,ADJ_HIRE_DATE
    ,CURRENT_CO
    ,CURRENT_PAYROLL
    ,SPOUSE_DOB
    ,PIN
    ,ORIG_HEALTHCARE_DATE
    ,MAIL_CODE
    ,MIDDLE_NAME
    ,TRANSACTION_ID
FROM DEP_ORIG_DT
WHERE CURRENT_CO = '12345'
ORDER BY SSN

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You would use an analytic function:

select . . .,
       count(*) over (partition by ssn) as cnt_ssn
. . .

Upvotes: 3

Related Questions