473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

count distinct subquery

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
4 4807
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
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
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
Thanks Jeff!

Jan 17 '08 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: puskas | last post by:
I'm trying to make a report of users per hour (I have one that works for users per day) but when I execute the query I realize that I get more users than the ones I have here is my query ...
1
by: Steve Heath | last post by:
I have a query that provides detail for sales transactions meeting certain criteria (date, purchase type, etc.) I am creating a report based on that query, and I want to add a summary section. I...
1
by: Leny | last post by:
Hi, Since Access doesn't support COUTN DISTINCT, I'm facing a new problem I didn't take into account: how to get the count of PK's in a complex query. I create queries on the fly -this means I...
2
by: Michael Howes | last post by:
I have a single DataTable in a DataSet. It has 4 columns and i'd like to get a handful of counts of unique items in 3 of the 4 columns. Can a DataTables Select or Compute methods to COUNT DISTINCT?...
1
by: jerry.ranch | last post by:
I see that Access 2003 doesn't support count distinct?? I have a work around, but one statement would be preferably of course Jerry
1
by: aps786 | last post by:
Hi, There is a table where I store ipaddress and user who logged in from that IP. I have a query to findout all ipaddresses, from where diff users had made request. stat ------------ ip...
4
by: Mina Patel | last post by:
Hi trying to find the number of duplicate tuples/records in table based on multiple columns ie. select count(distinct list multiple column key ) from x where date = y HAVING ( COUNT(multiple...
0
by: tom booster | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.