By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,883 Members | 1,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,883 IT Pros & Developers. It's quick & easy.

count distinct subquery

P: n/a
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.
Jan 15 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Jan 15, 10:59 am, tom booster <ma.tech...@gmail.comwrote:
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
Jan 15 '08 #2

P: n/a
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 <jefftyz...@sbcglobal.netwrote:
On Jan 15, 10:59 am, tom booster <ma.tech...@gmail.comwrote:


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 -
Jan 15 '08 #3

P: n/a
On Jan 15, 11:58 am, tom booster <ma.tech...@gmail.comwrote:
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 <jefftyz...@sbcglobal.netwrote:
On Jan 15, 10:59 am, tom booster <ma.tech...@gmail.comwrote:
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
Jan 15 '08 #4

P: n/a
Thanks Jeff!

Jan 17 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.