Re: How to work around count discarding null rows?

Matt Novinger wrote:

> I am writing a view that looks takes a table and sorts based on 3
> qualifers and then returns a count of the number of entries in that
> category. For example, a database from a college with
> freshman/sophomore/junior/senior converted to grade equivalents,
> different majors and ethniticities, and the count of the students of
> that race, in that major, in that grade.
>
> Grade Major Ethnicity Count
> 14 12 5 13
> 15 17 4 7
> 15 18 5 0 <--- what I want to output
>
> My problem is I want to output a 0 for the count column where no
> students exist in that grade, major and ethnic group. Count throws
> away null rows like that, and I can't seem to get around this, even
> though it seems like a basic problem. I have tried NVL function, but
> count throws out the null data first, anyone have an idea?
>
> Thanks in advance!
> Matt Novinger
> Penn State Applied Research Labs

NVL the column value to zero before you count.

SELECT COUNT(NVL(fieldvalue, 0))

Daniel Morgan Received on Mon Oct 28 2002 - 10:30:57 CST