number of non-null columns by key (conclusion)

David Churn

number of non-null columns by key (conclusion)
I should have mentioned that we are running DB2 v6. The count(savings)
would have been the best solution but does not work for our DB2.

The final SQL looks something like this.

SELECT SUM(CASE WHEN CHECKING IS NULL
AND SAVINGS IS NULL THEN 0
WHEN CHECKING IS NOT NULL
AND SAVINGS IS NULL THEN 1
WHEN CHECKING IS NULL
AND SAVINGS IS NOT NULL THEN 1
ELSE 2 END )

We switched the query to explicitely check whether checking or savings was
null in case we found that one of these "middle" conditions was the most
likely so we could tune the query in the future. Our initial assumption is
that most of the records will have no accounts (ie. prospective customers).

Philip, Terry, & John, thank you for your help.

David
>From: "Philip, Sibimon" <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: number of non-null columns by key
>Date: Wed, 11 Dec 2002 17:07:36 -0500
>
>I think you can use Sum with Case function like
>
>Select sum( case when checking is not null and savings is not null then 2
> when checking is not null or savings is not null then 1
> else 0
> end)
>
>Thanks..sibi
>
>
>-----Original Message-----
>From: David Churn [mailto:[login to unmask email]
>Sent: Wednesday, December 11, 2002 03:39 PM
>To: [login to unmask email]
>Subject: number of non-null columns by key
>
>
>Greetings!
>
>I am trying to get a count of the number of non-null columns related to one
>key. We are trying to save the count of a few child table columns on the
>parent table using a trigger.
>
>In the case of bank accounts, I want to know how many accounts that I have
>open based on their balance where null is a closed account.
>
>In the following example, I have 2 accounts.
>person bank checking savings
>Dave 1st $200.00 -null-
>Dave 2nd -null- $100.00
>
>In the next example, I have 4 accounts.
>person bank checking savings
>Dave 1st $200.00 -null-
>Dave 2nd -null- $100.00
>Dave 5th 100.00 50.00
>
>My first thought is to take the sum/avg. What kind of rounding grief will
>I
>be buying based on this? Has anyone tried to keep this kind of information
>before without programs cluttering up the picture?
>
>Thanks in advance,
>David

_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail