1

I'm trying to make a query where I'll have the id and the number of employees that person is their superior, also people that aren't managers, should appear and have a count of zero.

This is what I did but those that aren't managers show up with a count of null:

create view scnt as (
    select E1.super, count(distinct E1.id) cnt
    from Employee E1 left outer join Employee E2
    on E1.super = E2.id
    group by E1.super
);

create view numSuper as (
    select E.id, S.cnt
    from Employee E left outer join scnt S
    on S.super = E.id or E.id = null
);

select * from numSuper

Here That's the schema:

1

but we can ignore all the noise and use just what we need: employee(id, super)

Here's a fiddle with the inserts: http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=4c08869d0e8eba6c82fd4957092379a1

Any ideas on how to do this?

5
  • count(_) does never return NULL. See When does COUNT(*) return NULL?. You can have no rows, which is not the same as NULL.
    – joanolo
    Mar 7 '17 at 21:50
  • @joanolo I saw that before asking, I still have no clue how to get past it here...
    – shinzou
    Mar 7 '17 at 22:06
  • 1
    Something is unusual in your JOIN condition the or E.id = null is never true if id is your PK. So, take it out. And also: your schema doesn't have any column named super, yet your query has it. For further help, you should provide CREATE statements for all your tables, plus some sample data, one query and both the result you get and the result you would like to get. The provided information is not enough...
    – joanolo
    Mar 7 '17 at 22:13
  • 1
    I edited the question with a fiddle and the super is short for superior, I try to remove all the noise. @joanolo
    – shinzou
    Mar 7 '17 at 22:19
  • 2
    Unrelated, but: the parentheses around the select of the view is totally useless Mar 7 '17 at 22:20
6

You need to join in the opposite way: managers LEFT JOIN employees

create view scnt as 
    select E2.id as super, 
           count(E1.id) as cnt
    from Employee E2                    -- managers
      left outer join Employee E1       -- employees
      on E1.super = E2.id
    group by E2.id
  ;

select * from scnt ;

dbfiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.