CODEWITHSUNDEEP
sqloracle-database
user1916528
user1916528

Reputation: 389

How to select based on the count of records and the value of a field in Oracle SQL?

When the count of A00_PNUM is > 1 I need to select only the record where the OWNS_IT value is Yes. Otherwise I just need to select the record.

Example pcom table:

A00_PNUM    |   EXP_DATE
-------------------------
C0100000963 |   06-JAN-19
C0200000845 |   06-JAN-19
C0200000847 |   06-JAN-19
C0200000849 |   06-JAN-19

Example pcov table:

A00_PNUM    |   OWNS_IT
----------------------
C0100000963 |   No
C0100000963 |   Yes
C0200000845 |   Yes
C0200000845 |   No
C0200000847 |   No
C0200000849 |   Yes

Desired output:

IA00_PNUM   |   EXP_DATE    |   OWNS_IT
---------------------------------------
C0100000963 |   06-JAN-19   |   Yes
C0200000845 |   06-JAN-19   |   Yes
C0200000847 |   06-JAN-19   |   No
C0200000849 |   06-JAN-19   |   Yes

Here's what I am using now to select the records. I just don't know how to modify it by identifying the count of A00_PNUM and then selecting the record based on the OWNS_IT value of Yes.\

select  pc.a00_pnum
        ,pc.exp_date
        ,case pcov.a81
            when 'Y' then 'Yes'
            else 'No'
        end as owns_it
from pcom pc
join pcov
on pc.a00_pnum = pcov.a00_pnum
group by pc.a00_pnum
        ,pc.exp_date
        ,case pcov.a81
            when 'Y' then 'Yes'
            else 'No'
    end;

I am using Sql Developer to query an Oracle DB.

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In your case, you can use aggregation to get one row. MAX() returns the flag that you want, because 'YES' > 'NO':

select pcom.*, pcov.own_it
from pcom join
     (select A00_PNUM, max(owns_it) as owns_it
      from pcov
      group by A00_PNUM
     ) pcov
     on pcom.A00_PNUM = pcov.A00_PNUM;

Upvotes: 1

Related Questions