Toad World® Forums

Counting rows of data based on certain criteria

Greetings!

Below you will find the script I am working on to get desired result. After you have gone through the script, you will see the explanation for the desired result I am looking for.

SELECT SFRSTCR_USER REGISTRAR,

SFRSTCR_TERM_CODE TERM,

SFRSTCR_PIDM PIDM,

SFRSTCR_RSTS_CODE REG,

count (SFRSTCR_RSTS_CODE) CNT

FROM SFRSTCR

WHERE SFRSTCR_TERM_CODE IN (‘201610’, ‘201615’)
AND SFRSTCR_RSTS_CODE IN (‘RE’, ‘RF’, ‘RW’, ‘DD’, ‘DC’, ‘DA’)

GROUP BY SFRSTCR_USER, SFRSTCR_TERM_CODE, SFRSTCR_PIDM, SFRSTCR_RSTS_CODE

ORDER BY PIDM

Result

Row
REGISTRAR
TERM
PIDM
REG
CNT
1
WWW2_USER
201610
33861
RW
5
2
REEMH
201610
38072
RE
2
3
WWW2_USER
201610
38072
RW
2
4
WWW2_USER
201610
40001
RW
1
5
WWW2_USER
201610
40001
RE
1
6
ALTUNIJI
201610
38091
RW
1
7
MALMARZOUQI2
201610
38091
RE
1
8
WWW2_USER
201610
38091
RW
2
9
AJAMOUR
201610
40024
RE
1
10
AALKETBI
201610
41219
DD
1
11
AALKETBI
201610
41219
RE
1
12
WWW2_USER
201610
41219
RE
2
13
WWW2_USER
201610
41219
RW
3

From the result, I want count of PIMDs for each registrar based on the following conditions:

  1. If there is only one row data for a given PIDM, that PIDM should be counted once wth the registrar associated with that PIDM. (Rows 1 & 9 )

  2. If there is more than one row of data for the same PIDM, and the registrar is the same, that PIDM should only be counted once for that registrar (Rows 4-5).

  3. If there is more than one row of data for the same PIDM, and more than one registrar, then that PIDM should only be counted once and it should be associated in a new catwgory titled “Mixed” (Rows 2-3 & 10-13).

Any assistance provided would be appreciated.