How to Return Data Where Null means Zero?

Question

I have a table showing the count of occurrences of words in a text at certain data points. Here is a simplified example:

Word   Chapter   Count
dog    1         3
dog    2         7
dog    3         1
cat    2         4

Notice that there is no row for 'cat' in chapters 1 and 3, because the word was not used there.

I need to SELECT INTO a temp table (in prep for other aggregation, etc.) the above data, but I need 'cat' to show up for chapters 1 and 3 with a count of 0. The result should be:

Word   Chapter   Count
dog    1         3
dog    2         7
dog    3         1
cat    1         0
cat    2         4
cat    3         0

Any tips would be much appreciated. Thanks.

For the count that you are attempting to return, are there words for which you would want a count that are not present in the current table? Also, is it possible that there are chapters that have no entry in the table you're working with? For example, is there Chapter 4, which does not have either `cat` or `dog` in it, and so has no entry in your table? — pcurry, Apr 24 '13 at 19:47
I realize that I worded this poorly. The solutions suggested don't work because I actually don't even have a NULL to convert to a 0. Neither ISNULL, nor COALESCE work, because the rows don't exist. I attempted to create a temp table of just chapters to force one row per chapter per word, but depending on the join I either get far too many, or too few rows. — HumanJHawkins, Apr 24 '13 at 19:53
@pcurry, There are actually 14 data points analogous to chapters, millions of character strings analogous to words, and it is a floating point frequency rather than a count. I'm just trying to keep it simple. I am pretty sure that if I can figure out how to get the rows for 'cat' chapter 1 and 3 to show up (without adding false data such as a row for 'cat' that actually contains the counts for 'dog'), I can apply the principle to my real data. — HumanJHawkins, Apr 24 '13 at 19:59

score 3 · Accepted Answer · answered Apr 24 '13 at 20:18

I don't know your data structure, but I think what you are trying to do is:

create table Chapters (Chapter int);
insert Chapters values (1);
insert Chapters values (2);
insert Chapters values (3);

create table Words (Word varchar(50));
insert into Words values ('dog');
insert into Words values ('cat');

create table Chapters_Words (Word varchar(50), Chapter int, [Count] int);
insert into Chapters_Words values ('dog', 1, 3);
insert into Chapters_Words values ('dog', 2, 7);
insert into Chapters_Words values ('dog', 3, 1);
insert into Chapters_Words values ('cat', 2, 4);

select
    f.Word, 
    f.Chapter, 
    isnull(w.[Count], 0) [Count]
from
    Chapters_Words w
    right join (
        select w.Word, c.Chapter
        from Chapters c
        cross join Words w
    ) f on f.Chapter = w.Chapter and f.Word = w.Word

Result:

Word                                               Chapter     Count
-------------------------------------------------- ----------- -----------
dog                                                1           3
dog                                                2           7
dog                                                3           1
cat                                                1           0
cat                                                2           4
cat                                                3           0

Thanks much. That appears to work. CROSS JOIN was the piece I was missing. — HumanJHawkins, Apr 24 '13 at 21:04

score 1 · Answer 2 · edited May 23 '17 at 10:25

1

Null does NOT mean zero, nor does "zero" mean null.

Sigh...

Having said that, the "coalesce()" function is a Pop Favorite, depending on your RDBMS implementation: COALESCE with NULL.

See also SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions

edited May 23 '17 at 10:25

Community

1
1

answered Apr 24 '13 at 19:31

paulsm4

114,292
17
138
190

I understand that NULL does not mean zero typically, but in this case it does. Meaning, the lack of a value in this case is conclusive evidence that the count is zero. Thanks for the tip. – HumanJHawkins Apr 24 '13 at 19:45

score 0 · Answer 3 · answered Apr 24 '13 at 19:32

0

I believe you need COALESCE

COALESCE(Count, 0)

Full example:

SELECT Word, Chapter, COALESCE(Count, 0)
FROM YourTable

answered Apr 24 '13 at 19:32

Darren

68,902
24
138
144

Because there is actually no row for 'cat' at chapter 1, this does not work. I gave this a try, and the output is the same as a straight-up select. Thanks though. – HumanJHawkins Apr 24 '13 at 20:01

score 0 · Answer 4 · answered Apr 24 '13 at 19:34

0

Depends on what you are doing? You can use an outer join if the rows are dropping out. In Oracle you can nvl() to change null to something else. For example, to zero in a sum.

answered Apr 24 '13 at 19:34

asusu

321
1
5

How to Return Data Where Null means Zero?

4 Answers4