How to count distinct 2+ columns in Dremio?

1

Can only count distinct 1 column

SELECT

COUNT(DISTINCT MerchantKey), count(*)

FROM “hive_source.sapo_dw”.“dimmerchant”

;

But when I tried with 2 or more columns, it didn’t work. While in other SQL engines like MySQL, Postgresql, MS SQL Server it works fine.

SELECT

COUNT(DISTINCT MerchantKey, MerchantId), count(*)

FROM “hive_source.sapo_dw”.“dimmerchant”

;

SQL Error: PLAN ERROR:
Failure finding function: count(int32, int32).

SQL Query

SELECT

COUNT(DISTINCT MerchantKey, MerchantId), count(*)

FROM “hive_source.sapo_dw”.“dimmerchant”

ErrorOrigin: COORDINATOR
[Error Id: 7e80c92d-10f0-417e-82cc-ae93a8040bd1 ]

(com.dremio.exec.exception.SchemaChangeException)
Failure finding function: count(int32, int32).

2

@quangbilly79

Something like this?


SELECT count(DISTINCT department_id), count(DISTINCT job_id) FROM "employees.parquet"
3

I mean for the data below

department_id, job_id

1,1

1,2

2,1

2,1

There are 3 distinct pairs, 4 pair in total

1,1

1,2

2,1

So in MySQL or Spark, something like
COUNT(DISTINCT department_id, job_id), count(*)

got

3 and 4

image
image1149×279 19.9 KB

4

@quangbilly79
It’s not the nicest way, but how about:

SELECT COUNT(DISTINCT CONCAT(department_id, '-', job_id)) AS distinct_department_job_count, COUNT(*) AS total_count
FROM "employees.parquet";
5

Yep, need to work the longer way. Hope to get sth like count(distinct expr, expr,…) like spark and mysql (and some other sql engines) soon