Count and list rows? pl/sql

user_13096472 0
Viewed 320

I am trying to count all rows that meet a criteria, and then also have it list sequence numbers for each row so we can quickly identify those meeting the criteria. Ex: We know 5 meet criteria of Complete Response and we want to know there are 5 but also what those sequence numbers are. How would i write that in my query?

select 
  count(sf.protocol_subject_id) as responseCount, sf.BEST_RESPONSE
from oncore.sv_sub_followup sf
where sf.protocol_no = $P{pPclNo} and sf.best_response is not null
group by sf.best_response
order by decode(best_response, 'Complete Response', 1, 'Partial Response', 2, 'Stable', 3, 'Progressive', 4, 'Not Evaluable', 5)

I should also mentioned that this is one of 9 subreports compiling a larger report. Not sure if that makes a difference when trying to do this. I'm very new to pl/sql. Thanks in advance for any help.

count list oracle
2 Answers

If I got this right, you need to count the rows for each BEST_RESPONSE for a given PROTOCOL_NO, and also list the IDs (protocol_subject_id) of those rows. You could use the analityc function LISTAGG, that concatenates field values over a group. Something like this:

SELECT
    best_response,
    COUNT(protocol_subject_id) AS responsecount,
    LISTAGG(protocol_subject_id, ', ') WITHIN GROUP (
        ORDER BY
            1
    ) matching_rows_ids
FROM
    sv_sub_followup
WHERE
    protocol_no = :protocol_no
AND best_response IS NOT NULL
GROUP BY
    best_response
ORDER BY
    decode(best_response, 'Complete Response', 1, 'Partial Response', 2,
       'Stable', 3, 'Progressive', 4, 'Not Evaluable',
       5);
user_26077140

You can use a subquery to obtain your results like the below

        SELECT   responsecount,
             sf1.best_response,
             sf1.protocol_subject_id
             (
                      select   count(sf.protocol_subject_id) AS responsecount,
                               sf.best_response
                      FROM     oncore.sv_sub_followup sf
                      WHERE    sf.protocol_no = $p{ppclno}
                      AND      sf.best_response IS NOT NULL
                      GROUP BY sf.best_response)rec,
             oncore.sv_sub_followup             sf1
    WHERE    sf1.protocol_no=$p{ppclno}
    AND      sf1.best_response=rec.best_response
    ORDER BY instr('Complete Response,Partial Response,Stable,Progressive,Not Evaluable',sf1.best_response);
user_12977920
Related