vertica

sreeblr

i am trying to get cube with distinct of counts of two columns. However for cube i am getting error

[Vertica][VJDBC](6549) ERROR: DISTINCT Aggregates are not allowed with MultiLevel Aggregates

Is there way to get counts for distinct values when using cube .

 

SELECT REQUEST_ID,

       MEMBER_GENDER,

       MEMBER_RISK_SCORE,

       MEMBER_AGE_GROUP,

       CLAIM_TYPE_CD,

       PROGRAM_CD,

       CSASCTN.SVCTP_ID,

       COUNT(distinct  MEMBER_ID),

       COUNT(distinct SRO.CLAIM_ICN),

       COUNT(SRO.LINE_ID),

       SUM(DETAILS_QTY_BILLED),

       SUM(DETAIL_QTY_ALOWD),

       SUM(DETAIL_BILLED_AMT),

       SUM(DETAIL_ALOWD_AMT),

       SUM(DETAIL_PAID_AMT)

FROM SRO SRO

     JOIN CLSA CSASCTN ON SRO.CLAIM_ID = CSASCTN.CLAIM_ID AND SRO.LINE_ID = CSASCTN.LINE_ID

WHERE REQUEST_ID = ''

GROUP BY CUBE(REQUEST_ID, MEMBER_GENDER, MEMBER_RISK_SCORE, MEMBER_AGE_GROUP, CLAIM_TYPE_CD, PROGRAM_CD, CSASCTN.SVCTP_ID)