CODEWITHSUNDEEP
countdistinctsnowflake-cloud-data-platform
Jonito
Jonito

Reputation: 459

select count distinct over another value doesn't work

I don't know why Snowflake can't calculate my value. This table contains multiple periods for multiple Users, and a user can have X times the same period.

SELECT DISTINCT("Period"),COUNT(DISTINCT"Users")
FROM "DB"."SCHEMA"."TABLE"

I get the following result:

SQL compilation error: ["TABLE"."Period"] is not a valid group by expression

What's wrong with my statement?

Upvotes: 0

Views: 41

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

Fixed:

SELECT "Period", COUNT(DISTINCT"Users")
FROM "DB"."SCHEMA"."TABLE"
GROUP BY "Period"

Once you want to get a COUNT(), SELECT DISTINCT won't work anymore - and instead you have to GROUP BY.

Upvotes: 3

Related Questions