0

I want to query for each distinct user and their count which logged In as Yes

Userid    loggedIn   
----    -----   
1   Yes       
1   yes       
1   No    
2   Yes         
2   No          
3   No          
4   yes        
5   yes        
1   No

Output Should be

Userid    Count   
----    -----   
1   2       
2   1          
3   0          
4   1        
5   1
anonymous
  • 1
  • 1

1 Answers1

-1
with main as (

select distinct Userid from <table>
) 
select
    main.Userid,
    count(*) as total
from main
left join <table_name>
on main.Userid = <table_name>.Userid
and loggedIn = 'yes'
group by main.Userid

or

select
user_id,
sum(case when lower(loggedIn) = 'yes' then 1 end) as count_
from <table_name>
group by 1
trillion
  • 1,207
  • 1
  • 5
  • 15
  • Using `lower(loggedIn)` is unnecesary because SQL is case-insensitive by default. – Dai Nov 12 '22 at 02:06
  • Also, if `loggedIn <> 'yes'` then your `CASE` expression yields `0`, but `COUNT( 0 )` behaves the same as `COUNT( 1 )` - `COUNT` only considers `NULL` vs. non-`NULL`. I assume you're thinking of `SUM` instead. – Dai Nov 12 '22 at 02:07
  • `group by 1` <-- This is a bad practice in SQL: https://stackoverflow.com/questions/7392730/what-does-sql-clause-group-by-1-mean - always use named columns. – Dai Nov 12 '22 at 02:08
  • it is not always necessary that it will accept both Yes or yes, depending on the column type etc. For the group by it is more of a personal choice. – trillion Nov 12 '22 at 17:53