How avoid - User defined aggregate cannot be used in query with other distinct aggregates

Question

I have error

[0A000][5366] [Vertica]VJDBC ERROR: User defined aggregate cannot be used in query with other distinct aggregates

when I execute

SELECT LISTAGG(DISTINCT a USING PARAMETERS separator=',', max_length = 10) AS a,
       COUNT(DISTINCT b)                                                   AS b
FROM t

Do I have a way to avoid this aggregation and have the same logic?

In real project I have a complex query with many "subselect" and "with as", "group by" logic, so I wonder how it is possible to refactor, how many way I have?

I read the links, but no ideas how combine COUNT DISTINCT and LISTAGG DISTINCT: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm

DB: Vertica Analytic Database v10.1.1-3

UDP: solution with the help of @Kendle

(WITH zz as (SELECT a, b FROM mkt_dwh.t) -- "where", "group by" logic
 SELECT a, b
 FROM (WITH aa AS (SELECT DISTINCT a FROM zz),
            bb AS (SELECT COUNT(b) as b FROM zz)
       SELECT LISTAGG(distinct aa.a USING PARAMETERS separator=',', max_length = 1000) AS a,
              bb.b                                                                     AS b
       FROM aa,
            bb
       GROUP BY bb.b) as test);

CREATE TABLE t
(
    a VARCHAR,
    b VARCHAR
);


INSERT INTO mkt_dwh.t
VALUES ('1', '1');
INSERT INTO mkt_dwh.t
VALUES ('1', '1');
INSERT INTO mkt_dwh.t
VALUES ('2', '2');
INSERT INTO mkt_dwh.t
VALUES ('2', '3');
INSERT INTO mkt_dwh.t
VALUES ('3', '3');

SELECT LISTAGG(a USING PARAMETERS separator=',', max_length = 1000) AS a,
       COUNT(distinct b)                                            AS b
FROM mkt_dwh.t;
select count(*)
FROM mkt_dwh.t;

SELECT LISTAGG(distinct aa.a USING PARAMETERS separator=',', max_length = 1000) AS a,
       COUNT(bb.b)                                                              AS b
FROM (SELECT DISTINCT a FROM mkt_dwh.t) aa,
     (SELECT DISTINCT b FROM mkt_dwh.t) bb;

(WITH zz as (SELECT a, b FROM mkt_dwh.t) -- some where, group by logic
 SELECT a, b
 FROM (WITH aa AS (SELECT DISTINCT a FROM zz),
            bb AS (SELECT COUNT(b) as b FROM zz)
       SELECT LISTAGG(distinct aa.a USING PARAMETERS separator=',', max_length = 1000) AS a,
              bb.b                                                                     AS b
       FROM aa,
            bb
       GROUP BY bb.b) as test);

Answer 1

You could use sub-queries to do the distinct.

SELECT LISTAGG(aa.a USING PARAMETERS separator=',', max_length = 10) AS a,
       COUNT(bb.b)                                                   AS b
FROM 
  (SELECT DISTINCT a FROM t) aa,
  (SELECT DISTINCT b FROM t) bb;