CODEWITHSUNDEEP
oracle-databasejoin
Ciaran Martin
Ciaran Martin

Reputation: 598

Oracle Inner Table Query

I am trying to write a query in Oracle to give totals of active legal entities and inactive legal entities.

The query so far I have is:

    select le.Name, b.LE_ID, count(*) As TOTAL, dead.LE_ID as DEAD
    from BOOK b
    left join Legal_Entity le on le.LE_ID = b.LE_ID
    left join 
    (
        select count(LE_ID) as LE_ID
        from BOOK 
        where (Name like '%DUMMY%' or name like '%TEST%' or name like '%DEAD%' or name like '%DO NOT%' or status <> 'Active')
    ) dead on dead.LE_ID = b.LE_ID
    where b.LE_ID = 1234
    group by le.Name, b.LE_ID, dead.LE_ID
    order by b.LE_ID;

The results I am expecting are:

    Name        EntityID    Total   Dead

    Entity A    1234        500     200

i.e. for Book.LE_ID = 1234 I would like one row in the result set and a column with the Total number i.e. select * from Book where LE_ID = 1234 and a column with the number of dead books i.e. the inner query

But at the moment, my query is returning NULL for the number of dead rows.

The inner query is working without issue, but I'm clearly missing something here.

Upvotes: 0

Views: 301

Answers (2)

stee1rat
stee1rat

Reputation: 730

I am not sure, but shouldn't it be something like this?

  select le.Name, b.LE_ID, count(*) As TOTAL, dead.DEAD as DEAD
    from BOOK b
    left join Legal_Entity le on le.LE_ID = b.LE_ID
    left join 
    (
        select LE_ID, count(LE_ID) as DEAD
        from BOOK 
        where (Name like '%DUMMY%' or name like '%TEST%' or name like '%DEAD%' or name like '%DO NOT%' or status <> 'Active') 
        group by LE_ID
    ) dead on dead.LE_ID = b.LE_ID
    where b.LE_ID = 1234
    group by le.Name, b.LE_ID, dead.LE_ID
    order by b.LE_ID;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

It is a bit hard to figure out exactly what you are trying to do. After all, you are trying to join between a count and an id, something that is not likely to lead to success.

Your example has a name 'Entity A'. By the rules specified in the query for "dead" entities, there should be none. The name doesn't match this rule.

In all likelihood, you want conditional aggregation. Here is a query that I think gets the totals over all entities:

select count(*) As TOTAL, 
       sum(case when Name like '%DUMMY%' or name like '%TEST%' or name like '%DEAD%' or name like '%DO NOT%' or status <> 'Active'
                then 1 else 0
           end) as dead
from BOOK b left join
     Legal_Entity le
      on le.LE_ID = b.LE_ID;

Upvotes: 0

Related Questions