Values in one Column different but second column the same SQL

Question

I have an initial query written below and need to find values in the quote_id column that different but the corresponding values in the benefit_plan_cd column are the same. The output should look like the below. I know the prospect_nbr for this issue which is why I am able to add it to my initial query to get the expected results but need to be able to find other ones going forward.

select prospect_nbr, qb.quote_id, quote_type, effective_date, 
       benefit_plan_cd, package_item_cd
from   qo_benefit_data qb
       inner join
       qo_quote qq        on qb.quote_id = qq.quote_id
where  quote_type = 'R' 
  and  effective_date >= to_date('06/01/2022','mm/dd/yyyy')
  and  package_item_cd = 'MED'

Output should look like something like this excluding the other columns.

quote_id           benefit_plan_cd

514                      1234

513                      1234

Answer 1

Let's do this in two steps.

First take your existing query and add the following at the end of your select list:

select ... /* the columns you have already */
       , count(distinct quote_id partition by benefit_plan_id) as ct

That is the only change - don't change anything else. You may want to run this first, to see what it produces. (Looking at a few rows should suffice, you don't need to look at all the rows.)

Then use this as a subquery, to filter on this count being > 1:

select ... /* only the ORIGINAL columns, without the one we added */
from   (
           /* write the query from above here, as a SUBquery */
       )
where  ct > 1
;