Oracle SQL - CASE When condition for ANY record

Question

I have a DB that creates a record in a log table each time a document is run (opened). Each record contains the Doc_Name, the Doc_Owner, and the Doc_Run_by (i.e. the person that ran the document). I am trying to create a query to see each document name, and whether it has been run by anyone other than the owner (i.e. whether or not it has been shared).

Here is what the log table looks like:

+----------+-----------+------------+
| Doc_Name | Doc_Owner | Doc_Run_by |
+----------+-----------+------------+
| A        | Bob       | Bob        |
| A        | Bob       | Joe        |
| A        | Bob       | Sam        |
| A        | Bob       | Matt       |
| B        | Matt      | Matt       |
| B        | Matt      | Bob        |
| B        | Matt      | Joe        |
| C        | Sam       | Sam        |
| D        | Bob       | Bob        |
| D        | Bob       | Matt       |
+----------+-----------+------------+

Here is what I want the query output to look like:

+----------+--------------+
| Doc_Name | Share_Status |
+----------+--------------+
| A        | Y            |
| B        | Y            |
| C        | N            |
| D        | Y            |
+----------+--------------+

It would be something like this (in pseudo sql):

SELECT distinct Doc_Name, 
    CASE 
        WHEN Doc_Owner <> Doc_Run_by for any record of each doc_name
        THEN 'Y'
        ELSE 'N'
    END as Share_Status
FROM Log_Table;

Here is what I have so far:

SELECT distinct Doc_Name, 
    CASE 
        WHEN Doc_Owner <> Doc_Run_by
        THEN 'Y'
        ELSE 'N'
    END as Share_Status
FROM Log_Table;

Current Output:

+----------+--------------+
| Doc_Name | Share_Status |
+----------+--------------+
| A        | Y            |
| A        | N            |
| B        | Y            |
| B        | N            |
| C        | N            |
| D        | Y            |
| D        | N            |
+----------+--------------+

As you can see, it creates a separate record for each case. I understand why it is doing this, but I can't seem to figure out how to get my desired output. Thanks for any help that you can give.

Answer 1

You can use conditional aggregation:

SELECT Doc_Name, 
       CASE WHEN 
              COUNT(CASE WHEN Doc_Owner <> Doc_Run_by THEN 1 END) > 0 THEN 'Y'
            ELSE 'N'
       END AS Share_Status
FROM Log_Table 
GROUP BY Doc_Name

If there is even a single record (within a Doc_Name group of records) having Doc_Owner <> Doc_Run_by, then Share_Status will be equal to 'Y', otherwise it will be equal to 'N'.

Demo here