Count number of rows with having... SQL

Question

I have to count if UserId is listed one or more times and if one put +1 to "new users" else "+1" to "returning users".

Have I have done:

select 
Count(distinct [UserId]) as 'Unique users'
from [TelemetryData] 
where [DiscountId] = '8CAEA860-6766-43E2-9280-27AFE7FDF82E' and [EventName] = 'DiscountClick'

/* returning */
select
count(Id) as 'Returning users'
from [TelemetryData] 
where [DiscountId] = '8CAEA860-6766-43E2-9280-27AFE7FDF82E' and [EventName] = 'DiscountClick'
group by [UserId]
having count(Id) > 1

/* returning */
select
count(*) as 'New users'
from [TelemetryData] 
where [DiscountId] = '8CAEA860-6766-43E2-9280-27AFE7FDF82E' and [EventName] = 'DiscountClick'
group by [UserId]
having count(*) = 1

I need count total numer of rows in "returning" and "new" users query like in first query. How to do it?

This looks like SQL Server code so I added that tag.

Gordon Linoff
– Gordon Linoff

2016-10-19 10:49:39 +00:00
Commented Oct 19, 2016 at 10:49 — Gordon Linoff
– Gordon Linoff, Commented Oct 19, 2016 at 10:49

gofr1 · Accepted Answer · 2016-10-19 11:30:40Z

3

You can do this with two levels of aggregation. Calculate the count at the UserId level, then use that information to get the counts you want:

select sum(case when cnt > 1 then 1 else 0 end) as ReturningUsers,
       sum(case when cnt = 1 then 1 else 0 end) as NewUsers
from (select UserId, count(*) as cnt
      from [TelemetryData]
      where [DiscountId] = '8CAEA860-6766-43E2-9280-27AFE7FDF82E' and [EventName] = 'DiscountClick'
      group by UserId
     ) u

edited Oct 19, 2016 at 11:30

gofr1

16k11 gold badges47 silver badges56 bronze badges

answered Oct 19, 2016 at 10:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nerf Over a year ago

its not working 'cnt' not found. select sum(case when 'cnt' > 1 then 1 else 0 end) as 'ReturningUsers', sum(case when 'cnt' = 1 then 1 else 0 end) as 'NewUsers' from ( select [UserId], count(*) as 'cnt' from [TelemetryData] where [DiscountId] = '8CAEA860-6766-43E2-9280-27AFE7FDF82E' and [EventName] = 'DiscountClick' group by [UserId] )

gofr1 Over a year ago

@Nerf use whole query. with u in the end

Nerf Over a year ago

Oh right. What is 'u'? I thought it was misstake and removed it ;D

Nerf Over a year ago

Now how I can insert this select to another select? I'm getting error with "exists"...

Collectives™ on Stack Overflow

Count number of rows with having... SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related