detecting when NULL is eliminated from aggregate 1

[link9]

Hello all --

Is there someway that I can construct a query that will aggregate some values for me, but only return records where a NULL value was NOT eliminated from the aggregate?

ex)
My database has records that are uniquely identified by a unit number, but those units roll up into account numbers. Any one account number may have one or many units associated with it.

In a table, I am recording the number of returned surveys that people at an account have returned, and need to generate a report that only shows me records where all units have returned surveys. Problem is that the [return] table has all units already in there.

Until a unit has returned a survey, their [processed] column shows NULL -- when their surveys are processed, I plug in a value there.

So, if I could make a query that would aggregate by account number (summing the number of returned surveys), but only return records where all units had a number there, instead of one having a NULL, my life would be beautiful.

Thanks for any input.
Paul Prewett

 

[RickCole]

If I understand you correctly this should do the trick:

select account_number, sum(survey_value)
from units_table
where survey_value is not null
group by account_number

Rick.

 

[link9]

Sometimes you can't see the forest for the trees, ya know?

Thanks for pointing out the painfully obvious. I'm going to go bury my head in the sand now.


paul