number of non-null columns by key

David Churn

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

_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus



Terry Purcell

Re: number of non-null columns by key
(in response to David Churn)
Dave,

V7 has the COUNT(col) function. This differs from COUNT(*) or COUNT(DISTINCT
col) which we have always had.

It will count the number of non-NULL occurrences of a column.

Otherwise you can do SUM(CASE WHEN col IS NOT NULL THEN 1 END).

Such as:

SELECT keycol, COUNT(col)
FROM table
GROUP BY keycol

I think this is what you are asking for....

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of David Churn
Sent: Wednesday, December 11, 2002 3: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

_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus








Sibimon Philip

Re: number of non-null columns by key
(in response to Terry Purcell)
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

_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus








John McKown

Re: number of non-null columns by key
(in response to Sibimon Philip)
I'm not sure, but wouldn't:

SELECT PERSON, COUNT(CHECKING)+COUNT(SAVINGS)
FROM TABLE
GROUP BY PERSON

work?

--
John McKown
Senior Technical Specialist
UICI Insurance Center
Applications & Solutions Team
+1.817.255.3225


> -----Original Message-----
> From: David Churn [mailto:[login to unmask email]
> Sent: Wednesday, December 11, 2002 3: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