Handle Null values in SQL aggregated calculations

[Brian D]

SQL experts may be familiar with the need to do aggregate calculations
on fields.

I have a solution (below), but it seems unorthodox to have to go to
such an extraordinary length.

In this case, the SQL has to calculate the percentage of each race
(numerator) as the total across a series of columns that equal the
total population (denominator).

The problem is that some column categories contain Null values, so the
percentage calculation evaluates to Null -- actually, any Null value
in any column goofs up the percentage calculation across all other
categories.

The solution is to handle Null values inside of each (numerator)
column before attempting the percentage column for the respective
category.

For example (snipped from a page-length SQL GROUP BY statement
aggregating records to zip code areas):

Sum(IIF(Switch([ClientTrack_Unique_Clients].Ethnicity="Caucasian/
White",1)>0,1,0)) AS CCETHWHT, ...

([CCETHBLK] + [CCETHARB] + [CCETHASN] + [CCETHMRC] + [CCETHWHT] +
[CCETHHSP] + [CCETHIND] + [CCETHNAT] + [CCETHOTH]) AS CCETHTTL ...

And then the column containing the percentage calculation is simply:

[CCETHWHT]/[CCETHTTL] AS PCETHWHT

Unorthodox may be the use of both IIF and Switch functions (explained
because I arrived at the solution after iterative attempts). But also
unorthodox, it seems to me, is the need to run an evaluation on each
column before performing the percentage calculation.

Now, if the table from which the records are queried had, perhaps, a
default Zero value instead of allowing Nulls, the problem would go
away.

Can anyone think of an alternative approach?

It isn't urgent. I'm just curious to see if I might learn something
new.

Thanks,
Brian

[Access Developer]

It appears you have an un-normalized table design, and that often leads to
having to resort to "unusual" SQL methods. If you normalize by using a
row-wise ordering... instead of having multiple racial groups in a single
row, storing each in its own row, so that instead of Null, you just do not
have a record for that grouping.

If, indeed, the SQL you are using is Access' SQL, not generic SQL, the NZ
built-in function to convert Nulls to Zeros may serve your needs and avoid
your having to restructure your table design at this point.

But, as an aside, a proper relational design is likely to save later
troubles when you have to work with that table in the future.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Brian D" <brian...@gmail.com> wrote in message
news:c5c436e1-3b9a-4a68...@18g2000yqe.googlegroups.com...

[Jan T]

Hi Brian,


Try wrapping these fields in an NZ() function, which will return a
default value if the specified field is Null. For instance, NZ(MyField,
0) will return the value of MyField or a zero if it is Null. You can
also wrap NZ inside Sum, e.g. Sum(NZ(MyField, 0)).

Your query sounds rather complex. Have you tried using crosstab queries
to summarize/calculate these data ?


HTH

Jan

[Brian D]

Doh ... NZ function. Of course! I haven't used that in a while -- at
least, not in Access.

Thanks, Jan! Very much appreciated. I'll be revisiting this task
again, so it will come in handy.

[Brian D]

On Feb 8, 1:32 pm, "Access Developer" <accde...@gmail.com> wrote:
> It appears you have an un-normalized table design, and that often leads to
> having to resort to "unusual" SQL methods. If you normalize by using a
> row-wise ordering... instead of having multiple racial groups in a single
> row, storing each in its own row, so that instead of Null, you just do not
> have a record for that grouping.
>
> If, indeed, the SQL you are using is Access' SQL, not generic SQL, the NZ
> built-in function to convert Nulls to Zeros may serve your needs and avoid
> your having to restructure your table design at this point.
>
> But, as an aside, a proper relational design is likely to save later
> troubles when you have to work with that table in the future.
>
> --
> Larry Linson
>   Microsoft Office Access MVP
>   Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
>

> "Brian D" <brianden...@gmail.com> wrote in message

You're right, Larry. The data probably does look a little whacked. It
actually was normalized. I just needed to bump rows up into columns in
order to do a GIS join on the zip code, and symbolization on the
various fields. I'm definitely going to use the NZ function the next
time. I'd forgotten about that -- and was maybe too bleary after
sitting in front of a computer to even think outside the box anymore.

Thanks, Larry!

[Jan T]

Hi Brian,


Assuming that the data is indeed properly normalised, crosstabs queries
are probably the way to go as they are specifically intended to
transpose rows into columns and calculate totals to produces results
similar to an Excel pivot table.

A two-tier approach can be useful here by first creating a base-query to
produce a recordset that can then be easily transposed into columns by a
second (crosstab) query.


HTH


Jan

[Brian D]

Sorry to say, Jan, I've never been much good with crosstabs. Every
time I've tried them (including this go 'round), it always been a kind
of black box approach that I have difficulty visualizing. I generally
do better hand-coding my SQL and arranging the columns. In particular,
when I need to have unique zip codes down one column, with categorical
data counts (e.g., types of households counted by zip code) across the
horizontal axis, I haven't figured out how to do it. If you know of a
good resource, feel free to share. I'll continue to try.

It is a useful tip. I'm just not that useful ;-)

Brian

[Brian D]

On Feb 8, 2:50 pm, Jan T <access...@yahoo.com> wrote:

And, oh, Jan, I'd be happy to share my work with you if you care to
take a look. That would be well beyond the call of duty.

Brian