Summary Record

Hi Experts-

Please provide the possible solution for the below query-

Master_Table-

ID_Col Ref_Num Status
222 A123 P
222 A123 A
222 A123 F
222 A123 K
222 A123 T
111 B123 P
111 B123 T
111 B123 K
111 B123 F

Records with value A in the Status column (Master_Table) will only consider as Active records rest will be consider Inactive records.

From the above table, our script should give a single record per ID_col/Ref_Num based on the Status columns as below,

Subset_Master-

ID_Col Ref_Num No_Active_Status No_Inactive_Status Total_Status
222 A123 1 4 5
111 B123 0 4 4

For ID_Col 222 the No_Active_Status is 1 because this ID contains only 1 A, the rest will be consider No_Inactive_Status and the Total_Status is No_Active_Status+No_Inactive_Status.
For ID_Col 111 the No_Active_Status is 0 because there is no A status, the rest will be consider No_Inactive_Status and the Total_Status is No_Active_Status+No_Inactive_Status.

Thanks In advance.

-Balas

Check your database manual to see if it supports a CASE, IF or DECODE function in a column expression. Use it to check the status value and return either 0 or 1 in each column. Enclose each expression with a sum(...).

Thanks for your time and suggestion.