include zeros in count of groups

[purpledawn]

The issue I am having is with Oracle, and I think that it applies to ANSI SQL too. Please excuse me for not taking the time to do that research.

I have a table as follows

Field1 Field2
------ ------
A 1
A 4
B [null]
B [null]
C [null]

I am writing a query on this table as follows

SELECT field1, COUNT(field2)
FROM table
WHERE field2 BETWEEN 1 AND 3
GROUP BY field1

which gives the result

Field1 Count(Field2)
------ -------------
A 1

The problem is that I would like the result to look like

Field1 Count(Field2)
------ -------------
A 1
B 0
C 0

but I can't figure it out. I have tried the various functions you can do with count (ALL, *, DISTINCT), as well as NVL on both fields, but I either get errors or the same result. I also tried a really wacky idea of an outer self join, but that didn't work either.

Any suggestions?

 

[nagornyi]

SELECT field1, COUNT([b]nvl(field2,0)[/b])
FROM table
WHERE field2 BETWEEN 1 AND 3
GROUP BY field1

 

[purpledawn]

Thank you for your reply. I have tried nvl already, and it doesn't work. In my real table, field2 is actually a date field, but nvl(field2,'') and nvl(field2,null) both do not work either.

Any more suggestions?

 

[nagornyi]

Of course nvl(field2,'') and nvl(field2,null) would not work because they convert null to null. If your fields are dates, you need to use nvl(field2,some_date_here).

 

[r937]

this --

WHERE field2 BETWEEN 1 AND 3

is not going to return any rows where field2 is null

rudy

http://r937.com/

SQL Consulting

 

[PHV]

And what about this ?
SELECT DISTINCT A.field1,
(SELECT COUNT(*) FROM table B WHERE B.field1=A.field1 AND B.field2 BETWEEN 1 AND 3)
FROM table A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[purpledawn]

Thank-you for all your replies. As r037 sort of pointed out, my problem is not so much trying to count null records, as it is that I am trying to count some records that are excluded by the conditional statement, or that don't exist at all.

The best suggestion I have received for this problem was to use the following query:

SELECT SUM(CASE WHEN field2 BETWEEN 1 AND 3 THEN 1 ELSE 0 END) sumfield2
FROM table
GROUP BY field1

I don't know if the CASE statment is ANSI or Oracle specific, but it works well in this case (pun accidental). Thanks again for all your help.

 

[nagornyi]

CASE is not in ANSI standards, while NVL is in.
I am glad you solved your problem, but for pure academic interest I would still like to understand what's wrong with using NVL and replacing those nulls with some bogus dates which would for sure be out of the "1 to 3" range?
So purpledawn why do you think that

SELECT SUM(CASE WHEN field2 BETWEEN 1 AND 3 THEN 1 ELSE 0 END) sumfield2
FROM table
GROUP BY field1

is better then

SELECT field1, COUNT(nvl(field2,0))
FROM table
WHERE field2 BETWEEN 1 AND 3
GROUP BY field1

?

 

[purpledawn]

The NVL solution gives

Field1 Field2
------ ------
A 1

and the CASE solution gives

Field1 Field2
------ ------
A 1
B 0
C 0

.

 

[r937]

CASE is not in ANSI standards, while NVL is in

actually, i think it's the other way around



and as for why the CASE solution is better, the reason is, because the original poster actually did want the zero totals



rudy

http://r937.com/

SQL Consulting

 

[nagornyi]

OK. Now I see. Thanks. Sorry for misunderstanding.

 

[purpledawn]

Thanks again for your help.