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)
group cube with count distinct
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: group cube with count distinct
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):
This issue has been fixed in release 7.2.2.
From the release notes (https://my.vertica.com/docs/ReleaseNote ... .htm#7.2.2):
Example:Multilevel aggregation now supports SELECT ... DISTINCT and aggregate functions that specify the DISTINCT option (AVG, COUNT, MIN, MAX, and SUM).
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.