user1819227 user1819227 - 1 month ago 8
SQL Question

Show COUNT of each possible grade for an employee, showing zero when there are no grade entries

I have only one table available. I want to show the grade and the count of the number of times an employee has that grade recorded, but it must show a 0 for the grade if there are no records for that employee. I know how to do this using left join when two tables are present, but I only have 1 table.

How is this possible?

For example:

TABLE

empID | dept | grade
1 | 11 | a
2 | 11 | a
3 | 11 | b
1 | 22 | c
2 | 22 | f
3 | 22 | d
1 | 33 | a
2 | 33 | a
3 | 33 | a


If I run
SELECT grade, count(grade) from table where empID = 1 Group by grade;
, for example, it ends up printing out only grades the employee got and the count. Now I want to also print out the 0s for grades the employee did not have.

Answer

i think you're asking for this?

SQL> select e.grade, count(e2.empid)
  2         from (select distinct grade from e) e
  3              left outer join e e2
  4                           on e2.grade = e.grade
  5                          and e2.empid = 1
  6        group by e.grade
  7        order by grade;


G COUNT(E2.EMPID)
- ---------------
a               2
b               0
c               1
d               0
f               0

or as you have no rows with "e" grade then if you have a lookup table called grade:

SQL> select * from grade;

G
-
a
b
c
d
e
f

SQL> select e.grade, count(e2.empid)
  2    from grade emp
  3          left outer join emp e2
  4                       on e2.grade = e.grade
  5                      and e2.empid = 1
  6    group by e.grade
  7    order by grade;

G COUNT(E2.EMPID)
- ---------------
a               2
b               0
c               1
d               0
e               0
f               0