Replicating multiple count distinct in a single SQL statement

Question

Hi,

I have a sql statement:

Select count(distinct case when fail_ct > 0 then 1 else 0) as failed,

Count(distinct case when price_ct > 0 then 1 else 0) as Price

From <table_name>;

The output I get is -

Failed 8

Price 10

The input data format is -

Order-id | fail_ct | price_ct

O1 | 0 | 2

O2 | 8 | 0

O3 | 8 | 2

Expected output -

Failed 2

Price 2

How can the same thing be achieved in alteryx?

Thanks!

apathetichell · Answer

Are you trying to fix an issue with your sql statement or do this in Alteryx ---- If it's the former: Select count(distinct case when fail_ct > 0 then 1 else 0) as failed, Count(distinct case when price_ct > 0 then 1 else 0) as Price From will not compile in most DBs. It's not good SQL you need to use CASE WHEN THEN ELSE END. You have no END. should be: select count(distinct failed), count(distinct Price) from ( select case when fail_ct > 0 then 1 else 0 END as failed, case when price_ct > 0 then 1 else 0 END as Price FROM )

GrowthNatives · Answer

Hi @abhinabaroy09,

You can replicate COUNT(DISTINCT CASE WHEN ...) logic in Alteryx like this:

* Use a Filter Tool to create two streams:* One where fail_ct > 0

* One where price_ct > 0

* Add a Summarize Tool to each stream:* Group by Order-id

* Use Count Distinct on Order-id

This gives you the distinct count of orders where each condition is true, just like your SQL output.

">⭐Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀