# How to create distinct sum

**URL:** <https://matillioncommunity.discourse.group/t/how-to-create-distinct-sum/1837>\
**Category:** Matillion ETL\
**Created:** [August 1, 2022, 11:19am UTC](https://matillioncommunity.discourse.group/t/how-to-create-distinct-sum/1837 "2022-08-01T11:19:40Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![tanja.savic](https://avatars.discourse-cdn.com/v4/letter/t/5f9b8f/32.png) [@tanja.savic](https://matillioncommunity.discourse.group/u/tanja.savic)\
**Post date:** [August 1, 2022, 11:19am UTC](https://matillioncommunity.discourse.group/t/how-to-create-distinct-sum/1837/1 "2022-08-01T11:19:40Z")

</div>

Hi,

&nbsp;

I have query like this:

&nbsp;

SELECT col1

,SUM(col2)

,SUM(DISTINCT col3)

FROM table

GROUP BY col1

&nbsp;

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

&nbsp;

&nbsp;

---

<div class="post-metadata">

**Author:** ![Adwait\_K](https://avatars.discourse-cdn.com/v4/letter/a/bc8723/32.png) [@Adwait\_K](https://matillioncommunity.discourse.group/u/Adwait_K)\
**Post date:** [August 1, 2022, 8:20pm UTC](https://matillioncommunity.discourse.group/t/how-to-create-distinct-sum/1837/2 "2022-08-01T20:20:06Z")

</div>

Hi [@tanja.savic](https://matillioncommunity.discourse.group/u/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.

---

<div class="post-metadata">

**Author:** ![tanja.savic](https://avatars.discourse-cdn.com/v4/letter/t/5f9b8f/32.png) [@tanja.savic](https://matillioncommunity.discourse.group/u/tanja.savic)\
**Post date:** [August 2, 2022, 9:24am UTC](https://matillioncommunity.discourse.group/t/how-to-create-distinct-sum/1837/3 "2022-08-02T09:24:00Z")

</div>

Thanks [@Adwait\_K](https://matillioncommunity.discourse.group/u/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