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. 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
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 -
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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
...
|
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...
|
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...
|
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?...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
| |