CODEWITHSUNDEEP
sqlamazon-redshift
user8834780
user8834780

Reputation: 1670

How to replace sum(count() over(partition by x))

Trying to figure out how to do the following: sum(case when count(goal_id) over (partition by user_id) > 1 then 1 else 0 end) as user_with_multiple_goals. This is obviously not supported- but I would prefer to not add more subqueries if possible in any way..

Any suggestions?

Table:

user_id goal_id
A       A
A       B
A       C
B       D
C       E
C       F

Output: 2

Upvotes: 0

Views: 721

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I am thinking:

select count(*)
from (select user_id, count(*) as num_goals
      from t
      group by user_id
     ) u
where num_goals > 1;

Or:

select count(distinct user_id)
from t
where exists (select 1 from t t2 where t2.user_id = t.user_id and t2.goal_id <> t.goal_id);

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

Based on your sample data, this can be simplified to a normal case/count:

SELECT user_id, 
       CASE WHEN COUNT(*) > 1 
            THEN 1 
            ELSE 0 
       END as user_with_multiple_goals
FROM yourTable
GROUP BY user_id

Post edit:

SELECT DISTINCT COUNT(*)
FROM yourTable
GROUP BY user_id
HAVING COUNT(*) > 1

Upvotes: 1

Related Questions