Replicating multiple count distinct in a single SQL statement

[abhinabaroy09]

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!

[GrowthNatives]

Hi @abhinabaroy09,

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

1. Use a Filter Tool to create two streams:

   • One where fail_ct > 0

   • One where price_ct > 0

2. 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! 🚀

[apathetichell]

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 <table_name> 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

<table_name>)