group cube with count distinct

Moderator: NorbertKrupa

Post Reply
sreediff
Newbie
Newbie
Posts: 18
Joined: Mon Mar 28, 2016 2:51 pm

group cube with count distinct

Post by sreediff » Mon Mar 28, 2016 3:08 pm

i was trying to create cube with data requiring count of distinct values of two columns . but getting error
[Vertica][VJDBC](6549) ERROR: DISTINCT Aggregates are not allowed with MultiLevel Aggregates . Is there any workaround or extension like this.

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)

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: group cube with count distinct

Post by JimKnicely » Wed Mar 30, 2016 11:34 pm

Hi,

This issue has been fixed in release 7.2.2.

From the release notes (https://my.vertica.com/docs/ReleaseNote ... .htm#7.2.2):
Multilevel aggregation now supports SELECT ... DISTINCT and aggregate functions that specify the DISTINCT option (AVG, COUNT, MIN, MAX, and SUM).
Example:

Code: Select all

dbadmin=> select version();
              version
------------------------------------
 Vertica Analytic Database v7.2.2-1
(1 row)

dbadmin=> select * from test;
 col1 | col2 | col3 | col4
------+------+------+------
 Row1 | Jim  |    1 |    1
 Row1 | Jim  |    1 |    1
 Row2 | Jane |    1 |    1
(3 rows)

dbadmin=> select col1, col2, sum(col3), sum(col4) from test group by col1, col2;
 col1 | col2 | sum | sum
------+------+-----+-----
 Row1 | Jim  |   2 |   2
 Row2 | Jane |   1 |   1
(2 rows)

dbadmin=> select col1, col2, sum(col3), sum(col4) from test group by cube (col1, col2);
 col1 | col2 | sum | sum
------+------+-----+-----
      | Jane |   1 |   1
      | Jim  |   2 |   2
      |      |   3 |   3
 Row2 |      |   1 |   1
 Row2 | Jane |   1 |   1
 Row1 | Jim  |   2 |   2
 Row1 |      |   2 |   2
(7 rows)

dbadmin=> select col1, col2, count(distinct col3), count(distinct col4) from test group by cube (col1, col2);
 col1 | col2 | count | count
------+------+-------+-------
      |      |     1 |     1
      | Jane |     1 |     1
 Row1 |      |     1 |     1
      | Jim  |     1 |     1
 Row2 | Jane |     1 |     1
 Row1 | Jim  |     1 |     1
 Row2 |      |     1 |     1
(7 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”