Re: How to work around count discarding null rows?

I must admit, I didn't study your question too hard and stopped when I saw you want a COUNT of zero but...

If you ever want you COUNT(*) to show a zero you generally ahve to think in terms of using SUM rather than COUNT.

Make sure that your query returns a 1 for each row to be included and a zero (probably using an outer join) for 'missing' rows. DECODE(pk_col, NULL, 0, 1) as an expression will likely do. SUM this and you should be on your way to getting COUNTs of zero.

HTH "Matt Novinger" <aeaas_at_yahoo.com> wrote in message news:19809582.0210280810.54b35ef6_at_posting.google.com...
> 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
Received on Wed Oct 30 2002 - 04:07:27 CST