Problem in the Query

[natncm70]

Hello,

I have query like this
SEL distinct
A.DOMICILE_CHANNEL_ID
, A.PRODUCT_ID
, COUNT(B.PARTY_ID) ,
COUNT( B.ACCOUNT_NBR)
FROM DT_VEDW.T0300_ACCOUNT A
INNER JOIN dt_vedw.t0328_party_account B
ON A.ACCOUNT_NBR = B.ACCOUNT_NBR
INNER JOIN DT_VEDW.B0200_PRODUCT_MAP C
ON A.PRODUCT_ID = C.MAPPED_ID
GROUP BY 1,2

In this query, I am getting same Party Id and Account Nbr is coming. Actually, I should get less number of Party's.
Because, party's has more number of Accounts.
What is the problem in the query. Please help me.

 

[natncm70]

Hello,
I got the answer. I have used "DISTINCT" in side the count. It is working fine. This is the query
SELECT
A.DOMICILE_CHANNEL_ID
, A.PRODUCT_ID
, COUNT (DISTINCT B.PARTY_ID) ,
COUNT( B.ACCOUNT_NBR)
FROM DT_VEDW.T0300_ACCOUNT A
INNER JOIN dt_vedw.t0328_party_account B
ON A.ACCOUNT_NBR = B.ACCOUNT_NBR
INNER JOIN DT_VEDW.B0200_PRODUCT_MAP C
ON A.PRODUCT_ID = C.MAPPED_ID
GROUP BY 1,2 /* MODIFIED ON 11-01-2003 */

 

[dnoeth]

Unless B.PARTY_ID or B.ACCOUNT_NBR is Nullable, COUNT(B.PARTY_ID) or COUNT(B.ACCOUNT_NBR) is the same as COUNT(*).

You probably have to move the DISTINCT into the COUNT:
SEL A.DOMICILE_CHANNEL_ID
, A.PRODUCT_ID
, COUNT(DISTINCT B.PARTY_ID) ,
COUNT(*)

If you really need two distinct counts
, COUNT(DISTINCT B.PARTY_ID) ,
COUNT(DISTINCT B.ACCOUNT_NBR)
you'll probably have use derived tables, because only V2R5 supports this.

Dieter