Count of distinct & non distinct

Hello Everyone,

I need some assistance on the below.

I’m using a table(Tab1) with 2 million records. I’m looking for Purchasing Document Number (EBELN) which has more than 1 entry and i need the count of Unique Document Numbers and the count of Documents having more than 1 entry.

I’m using the below Case statement

CASE WHEN COUNT (DISTINCT Tab1. EBELN) > 1 THEN 1.0 ELSE 0.0 END
this gives me 1 if the record is unique and 0 if not unique.

I need the total count of unique’s & non unique’s. When i try the below i’m getting an error saying Aggregate expressions cannot be nested.

SUM ( CASE WHEN COUNT (DISTINCT Tab1. EBELN) > 1 THEN 1.0 ELSE 0.0 END )

Appreciate the assistance.

Thanks!!

Regards,
Chanti

Hi Chanti :slight_smile:

Please try the following PQL code in separate components to calculate the desired KPIs:

Overall number of unique document numbers:
COUNT(DISTINCT Tab1.EBELN)

Overall number of non-unique document numbers:
COUNT(Tab1.EBELN) - COUNT(DISTINCT Tab1.EBELN)

Best regards,
Data Science Service Desk