count distinct subquery

[tom booster]

Hi All,

I'm trying to convert a T-SQl query to DB2.

I have two tables policy and policyHolder.
I would like a count of the amount of distinct poicyHolders per
policy, for a particular set of new policies.

select P.policyID, (select count(PH.PolicyHolderId) from PolicyHolder
PH where PH.policyID=P.policyID) CountPolicyHolders from Policy P
where P.policyID in (select policyID from NewPolicy)

OR

select P.policyID, (select count(PH.PolicyHolderId) from PolicyHolder
PH where PH.policyID=P.policyID) CountPolicyHolders from Policy P
inner join NewPolicy NP on (P.policyID=NP.policyID)
Any help would be appreciated.

[jefftyzzer]

On Jan 15, 10:59 am, tom booster <[e-mail]:

Hi All,

I'm trying to convert a T-SQl query to DB2.

I have two tables policy and policyHolder.
I would like a count of the amount of distinct poicyHolders per
policy, for a particular set of new policies.

select P.policyID, (select count(PH.PolicyHolderId) from PolicyHolder
PH where PH.policyID=P.policyID) CountPolicyHolders from Policy P
where P.policyID in (select policyID from NewPolicy)

OR

select P.policyID, (select count(PH.PolicyHolderId) from PolicyHolder
PH where PH.policyID=P.policyID) CountPolicyHolders from Policy P
inner join NewPolicy NP on (P.policyID=NP.policyID)

Any help would be appreciated.

I think this should do the trick:

SELECT
P.POLICYID,
COUNT(DISTINCT PH.POLICYHOLDERID)
FROM
POLICY P
JOIN
POLICYHOLDER PH
ON
PH.POLICYID = P.POLICYID
WHERE
P.POLICYID IN
(
SELECT
POLICYID
FROM
NEWPOLICY
)
GROUP BY
P.POLICYID

--Jeff

[tom booster]

Hi Jeff,
My confusion is about the group by....why is it needed.
what would I do if I needed more string fields returned from both
tables?
Isn't DB2 like T-SQL where all the fields in the select clause not in
the group by have to use an aggregation function?

Essentially I want the distinct count to be based not on the returned
set but on an arbitrary table of my choosing

Thanks
On Jan 15, 2:35*pm, jefftyzzer <[e-mail]:

On Jan 15, 10:59 am, tom booster <[e-mail]:

Hi All,

I'm trying to convert a T-SQl query to DB2.

I have two tables policy and policyHolder.
I would like a count of the amount of distinct poicyHolders per
policy, for a particular set of new policies.

select P.policyID, (select count(PH.PolicyHolderId) from PolicyHolder
PH where PH.policyID=P.policyID) CountPolicyHolders from Policy P
where P.policyID in (select policyID from NewPolicy)

OR

select P.policyID, (select count(PH.PolicyHolderId) from PolicyHolder
PH where PH.policyID=P.policyID) CountPolicyHolders from Policy P
inner join NewPolicy NP on (P.policyID=NP.policyID)

Any help would be appreciated.

I think this should do the trick:

SELECT
* * * * P.POLICYID,
* * * * COUNT(DISTINCT PH.POLICYHOLDERID)
FROM
* * * * POLICY P
JOIN
* * * * POLICYHOLDER PH
ON
* * * * PH.POLICYID = P.POLICYID
WHERE
* * * * P.POLICYID IN
* * * * * * * * (
* * * * * * * * SELECT
* * * * * * * * * * * * POLICYID
* * * * * * * * FROM
* * * * * * * * * * * * NEWPOLICY
* * * * * * * * )
GROUP BY
* * * * P.POLICYID

--Jeff- Hide quoted text -

- Show quoted text -

[jefftyzzer]

On Jan 15, 11:58 am, tom booster <[e-mail]:

Hi Jeff,
My confusion is about the group by....why is it needed.
what would I do if I needed more string fields returned from both
tables?
Isn't DB2 like T-SQL where all the fields in the select clause not in
the group by have to use an aggregation function?

Essentially I want the distinct count to be based not on the returned
set but on an arbitrary table of my choosing

Thanks

On Jan 15, 2:35 pm, jefftyzzer <[e-mail]:

On Jan 15, 10:59 am, tom booster <[e-mail]:

Hi All,

I'm trying to convert a T-SQl query to DB2.

I have two tables policy and policyHolder.
I would like a count of the amount of distinct poicyHolders per
policy, for a particular set of new policies.

select P.policyID, (select count(PH.PolicyHolderId) from PolicyHolder
PH where PH.policyID=P.policyID) CountPolicyHolders from Policy P
where P.policyID in (select policyID from NewPolicy)

OR

select P.policyID, (select count(PH.PolicyHolderId) from PolicyHolder
PH where PH.policyID=P.policyID) CountPolicyHolders from Policy P
inner join NewPolicy NP on (P.policyID=NP.policyID)

Any help would be appreciated.

I think this should do the trick:

SELECT
P.POLICYID,
COUNT(DISTINCT PH.POLICYHOLDERID)
FROM
POLICY P
JOIN
POLICYHOLDER PH
ON
PH.POLICYID = P.POLICYID
WHERE
P.POLICYID IN
(
SELECT
POLICYID
FROM
NEWPOLICY
)
GROUP BY
P.POLICYID

--Jeff- Hide quoted text -

- Show quoted text -

Perhaps a correlated scalar subselect will do the trick?

SELECT
P.POLICYID,
(SELECT COUNT( DISTINCT PH.POLICYHOLDERID) FROM POLICYHOLDER
PH WHERE PH.POLICYID = P.POLICYID)
FROM
POLICY P
WHERE
P.POLICYID IN
(
SELECT
POLICYID
FROM
NEWPOLICY
)

--Jeff

[tom booster]

Thanks Jeff!