Using count my tuples are coming out as 1's when a 0 should be present?

user_8722245 0
Viewed 27

I am trying to count how many times each ID appears in a specific table as follows.

Members
ID | NAME
1  | Bob
2  | John
3  | Sally
4  | Hannah

Department
ID | DEPT
 1 |  Math
 1 |  English
 3 |  Math
 4 |  Math
 4 |  English
 4 |  Science

I would like to display a table as follows:

ID | Name   | Count(Dept)
1  | Bob    |  2
2  | John   |  0
3  | Sally  |  1
4  | Hannah |  3

I have made several attempts at this my most recent one being:

select owner 
from table
where (select count(distinct letter) from table group by owner) in 
(select max(count(distinct letter)) from table group by owner);

I just cannot seem how to get the 0 results to show properly. Honestly, i'm kinda confused as to how to use on ... when you don't want to basically preform a natural join.

edit: I have edited the question to properly show what I am trying to ask. Sorry for any confusion.

oracle sql
1 Answers

When joining tables it is very important to reference WHICH TABLE each column comes from. To help abbreviate the overall SQL a "table alias" may be defined and used instead of the full table name. 

select m.id, m.name, count(d.id) as count_d
from members m
left outer join department d on m.id = d.id
group by m.id, m.name
;

In the above I have used a LEFT OUTER JOIN. Once you have that outer join defined there may be some rows where no row from department is matched to a row in membesr and you therefore would get NULLs in all columns related to departmenton that row.

The COUNT() function increments by 1 for every NON-NULL value it encounters, so what you include inside the parentheses is important. COUNT(*) will increment for every row no matter what. COUNT(d.id) may give zeros because some rows might be NULL in that column.

SEE this SQL Fiddle

Results:

| ID |   NAME | COUNT_D |
|----|--------|---------|
|  1 |    Bob |       2 |
|  2 |   John |       0 |
|  3 |  Sally |       1 |
|  4 | Hannah |       3 |
user_20677530
Related