How to create distinct sum

Matillion ETL
1

Hi,

 

I have query like this:

 

SELECT col1

,SUM(col2)

,SUM(DISTINCT col3)

FROM table

GROUP BY col1

 

How would you create this in Matillion workflow - i'm interested in sum distinct part?

 

 

2

Hi @tanja.savic​

You'd have to break up the two aggregations into two flows in your transformation job - one flow does the sum on col2. The second flow does the distinct on Col3 and then apply a sum to it.

Once you're performed the aggregations separately, then join them back using col1.

I basically got this idea because I took your query and expanded it into a query with CTEs. See the query below and it might give you some more ideas. Otherwise just slap your original query into a SQL input ;)

WITH a1

AS (SELECT col1,

Sum(col2) AS col2

FROM test1

GROUP BY col1),

a2

AS (SELECT col1,

Sum(DISTINCT col3) AS col3

FROM test1

GROUP BY col1)

SELECT a1.col1,

a1.col2,

a2.col3

FROM a1

INNER JOIN a2

ON a1.col1 = a2.col1

Not sure if this helps, but hopefully it sparks some ideas.

3

Thanks @Adwait_K​ ,

That helped. It is just weird that there is no SUM Distinct as build in functionality in Matillion for Redshift, and i see that exists in Matillion for Delta lake and Synapse. https://documentation.matillion.com/docs/1991880

Anyway, thanks a lot.

Regards